Forum Discussion
Solved
excel calculation not equalling what it should?
I have a simple spreadsheet or at least I thought it was!
I have cell E12 which is a formulated figure £61.99 Cell F12 is the amount paid £62.00 cell G12 is sum of F12-E12, which gives result of 0.02? should be 0.01 or at least it was when I went to school? I have tried formulating cell as currency to 2 decimal places and it still gives 0.02?
similarly cell e13 is £40.43, F13 is £42.00, G13 is 1.58 ?
Any ideas or suggestions to teach excel simple mathematics?
TIA
Change the formula to
=IF(F13<>"", ROUND(F13-E13, 2), "")
or
=IF(F13<>"", ROUND(F13, 2)-ROUND(E13, 2), "")
10 Replies
Thanks for your reply, I have only just found it as the mail notification went straight to Junk folder!
Cell E12 contains a formula to calculate a result.
Cell F12 is a data entry only.
Can I apply the Round function with an existing formula in same cell?
Yes, change
=expression
to
=ROUND(expression, 2)
here is the formula i have
=IF(F13<>"",(F13-E13),"")
Under the hood Excel uses more decimals for calculation than it shows in the cell (depending on the number format of the cell that is).
If E12 and F12 are the result of a calculation, they may actually contain 61.98500001 and 62.004321 respectively.
If you subtract those two numbers you end up with 0.0193209899999971, rounded to two decimals that is 2 cents.
Before subtracting both values, you must round them to two decimals if you want the cents to match up.
=ROUND(F12,2)-ROUND(E12,2)
should give you the correct number.
