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 Office
Satisfied Customers: 6587
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?
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 1 year ago.
Hello, thanks for the response here is the link
http://filesxpress.com/download-80fc3609661374e4c8a452f1210bc013
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: 6587
Experience: Computer Software Specialist for more than 10 years
Jess M. and 5 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year 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 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.
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    23
    A+ Comptia Certified computer repair
< Previous | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    23
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    241
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/JA/JACUSTOMERf8udkdxk/2013-8-3_15150_323738101505074393259301621172992o.64x64.jpg KamilAnwar1's Avatar

    KamilAnwar1

    Office Specialist

    Satisfied Customers:

    122
    8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    111
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/WI/Windowspcfix/2013-8-19_153826_ja12.64x64.jpg James K.'s Avatar

    James K.

    Consultant

    Satisfied Customers:

    110
    Technical Director of IT Company
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

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

Related Microsoft Office Questions