• 100% Satisfaction Guarantee

Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6195
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office 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: 1 year ago.
Category: Microsoft Office
Expert:  Jess M. replied 1 year 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 1 year ago.
Hello, thanks for the response here is the link
Thanks
Sas
Expert:  Jess M. replied 1 year 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 1 year 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 1 year 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., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6195
Experience: Computer Software Specialist for more than 10 years
Customer: replied 1 year ago.
Thanks dude. That was a great help.
Cheers
Sas.
Expert:  Jess M. replied 1 year 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

### What Customers are Saying:

• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
< Previous | Next >
• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
• Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
• This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex
• Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP
• I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin
• Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther
• Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.

• ### jstinehelfer

#### Satisfied Customers:

23
A+ Comptia Certified computer repair
< Previous | Next >

### jstinehelfer

#### Satisfied Customers:

23
A+ Comptia Certified computer repair

### JasonJames122

#### Satisfied Customers:

0
I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business

### Jess M.

#### Satisfied Customers:

233
Computer Software Specialist for more than 10 years

### KamilAnwar1

#### Satisfied Customers:

119
8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.

### Chris L.

#### Satisfied Customers:

111
Certified Expert with over 10 years experience.

### James K.

#### Satisfied Customers:

110
Technical Director of IT Company

### IT Miro

#### Satisfied Customers:

56
Bachelor's Degree in Information Technology, Microsoft Certified Professional