How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Jess M. Your Own Question
Jess M.
Jess M., Computer Support Specialist
Category: Microsoft
Satisfied Customers: 6959
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Question Here...
Jess M. is online now

I need help with calculating a formula using excel to

Customer Question

Hello I need help with calculating a formula using excel to calculate the running total on a mileage spreadsheet I keep getting the annoying error VALUE!. I got this spreadsheet from someone who knows excel so I have no idea if a comma, equals sign or parentheses is in the wrong place. Please advise. Please note it is only coming up on one row but when you try to copy it down the error is replicated. Any help is gratefully appreciated
Thanks
Sas.
Submitted: 2 years ago.
Category: Microsoft
Expert:  Jess M. replied 2 years ago.
Hi, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.
Can you please send me a copy or dummy of your Excel file so that I can locate the source of the error and fix it?
You can attach it here when you reply, or you can upload it to http://filesXpress.com and then give me the download link.
Thank you.
Best regards,
Jess
Customer: replied 2 years ago.
Hello, thanks for the response here is the link
http://filesxpress.com/download-80fc3609661374e4c8a452f1210bc013
Thanks
Sas
Expert:  Jess M. replied 2 years ago.
Hi Sas,
Thank you for the sample file. What exactly is the formula you are using to compute for the Running Total?
As I can see here, it has this pattern:
J3+I5
J4+I6
J5+I7
J6+I8
J7+I9
Is that correct?
Customer: replied 2 years ago.
Yes this is correct. I need a running total so I can see how much money my mileage claim is worth
Thanks
Sas.
Expert:  Jess M. replied 2 years ago.
Thank you Sas for that confirmation.
First, I corrected faulty formulas in column I (Mileage Claim Value). The old formula like in cell I5 was:
=IF(G510000,(F2*0.25),((G5-10000)*0.25)+((F2-(G5-10000))*0.45)))
In that formula, #REF! is an error, a form of a missing reference. Also, F2 in the formula will give you a #VALUE error because as you can see in the file that you gave me, F2 is the heading of the table containing the TEXT "Miles". So the part of the formula that says F2*0.45 will equate to "Miles"*0.45 which will give you the #VALUE error because the text cannot be multiplied to any number. I hope that make sense.
So I corrected the formula and will now show in I5:
=IF(G510000,(F5*0.25),((G5-10000)*0.25)+((F5-(G5-10000))*0.45)))
The #REF! there is actually the G column (G5 in the sample cell) since the first IF is checking G5. It simply says IF G5<10000, do this. In the other IF, it shall be testing G5 again. I hope that makes sense.
I also corrected the Running Total column. The main reason why you got the first #VALUE error is because you manually entered the £ sign. That made it a TEXT and not a number. It should be formatted as CURRENCY so that the sign is shown automatically. I already corrected that and made all changes as required.
Here is the final file:
http://filesxpress.com/d-d897b8e0
I hope that helped.
Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!
If you need assistance in the future, you can request me any time by posting a new question starting with "For Jess M" so that I can assist you immediately.
Thank you!
Best regards,
Jess
Jess M. and other Microsoft Specialists are ready to help you
Customer: replied 2 years ago.
Thanks dude. That was a great help.
Good to do business with you. Are you on facebook or linked in?
Cheers
Sas.
Expert:  Jess M. replied 2 years ago.
Hi Sas,
Thank you for writing back and for the rating. I am glad to be of help. Yes, I have accounts to the 2 sites you mentioned but unfortunately, we cannot share contact information here as it is against site policy.
Thank you very much.
Best regards,
Jess