• 100% Satisfaction Guarantee
Jess M., Computer Support Specialist
Category: Microsoft
Satisfied Customers: 7604
Experience:  Computer Software Specialist for more than 10 years
49766785
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?
Thank you.
Best regards,
Jess
Customer: replied 2 years ago.
Hello, thanks for the response here is the link
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