Formula not producing correct result

[Guest]

Hi! I have a formula applied to a column that is giving the correct results in some cells but not in others.

My first error is in J13 which shows an incorrect result of 1271. It should be 1260
J13 has formula: =IF(H13<=0,0,(H13*30)
H13 has a formula: =IF(F13=0,0,(($H$5*60)/D13)*F13) with a result of 42 (correct
H5 is 12 (no formula
D13 has formula: =IF(B13<=0,0,(B13+5)) with a result of 17 (correct
F13 has formula: =ServiceInv!B7 with a result of 1 (correct
B13 is 12 (no formula

Can anybody help me out here?
Thanks

 

[Jerry W. Lewis]

1270.588235 is correct. You probably have H13 formatted to hide the
decimal places, but its value is 42.35294118, not 42 (which would give
1260). Formats affect the display, but not the actual value. If you
want the values to be rounded, you must either explicitly round them
with the ROUND() funcdtion, or else select the menu item
Tools|Options|Calculation and check "Precision as displayed".

Jerry
Excel MVP

 

[JE McGimpsey]

The answer should be 1270.588235 not 1260.

XL's calculations work with the values stored in the cells, not the
values displayed.

Given your data, H13 = 42.35294118 and J13 = 1270.588235. You can
verify this by expanding the format to include more decimal places.

If you want the stored values to match the displayed values, you can
choose Tools/Options/Calculation and check the Precision as displayed
checkbox. However, that's a global setting and may affect other
calculations.

You could instead use the ROUND() function:

H13: =ROUND(MAX(0, $H$5*60/D13 * F13),0)