calculation not working

[jmergerner]

I am using two populated fields to perform a simple multiplication formula and it won't calculate without me changing one of the cells to a hardcoded entry.

Cell 1 =IF(J12>0,D16/E8,0) - D16 is $20,998.00 (Currency formatted)  E8 is 59.75 Number formatted

Cell 2 =J12 - J12 is 32 number derived from this formula =IF(F12>0,I12/F12,0) Number formatted

Cell 3 =(H15*H16) this is calculating Cell 1 & Cell 2.

Total should be $11,245.79

I keep getting $11,306.62 unless I manually change cell 2 to be 32

[#2]

@jmergerner 

This is probably because the result in J12 is not exactly 32.

Please attach a sample workbook or make it available through OneDrive, Google Drive, Dropbox or similar.

[#3]

@jmergerner 

Format all of the cells to display 13 decimal places, and you will probably see that some of the values are not what they appear to be with their original formats.

By "all" cells, I mean:  H15, H16, H17 ("cell 3"), D16, E8, J12, I12 and F12.

Your description lacks sufficient detail for us to do a sensitivity analysis easily. 

Most likely, all will work as expected if you change each formula so that it explicitly rounds to 2 decimal places (or however many decimal places that you require). 

By "explicit", I mean:  wrap ROUND(...,2) around the expressions.  Formatting to display 2 decimal places is usually not sufficient.

For example, the formula in H15 might become:

=IF(J12>0, ROUND(D16/E8 ,2), 0)

And the formula in J12 might become:

=IF(F12>0, ROUND(I12/F12, 2), 0)

For completeness, the formula in H17 ("cell 3") might become:

=ROUND(H15*H16, 2)

If you need further assistance, please provide an Excel file that demonstrates the problem (i.e. H17 errorneously displays 11306.62).

Ideally, attach the file to a response by clicking "browse" at the bottom of your reply window.

If you cannot do that, upload the file to a file-sharing website.  I like box.net/files; others like dropbox.com.  You might like onedrive.live.com because it uses the same login as this forum.

[#4]

@Joe User 

I tried your method and still get the same error in Cell H17 (highlighted in Orange).

Not sure if I did this correct but here is the link:  https://www.dropbox.com/scl/fi/xn7f3frk2j0y2aee0uu79/Test001.xlsx?dl=0&rlkey=hcxxgquz362at9hwzre2dla...

[#5]

@jmergerner 

If you format J12 as Number with 13 decimals, you'll see that its value is actually 32.1700000... instead of exactly 32. This causes the discrepancy.

So the result in H17 is correct if you take the real value of J12 into account.

If you need J12 to be a whole number for some reason, change the formula in that cell to

=IF(F12>0,ROUND(I12/F12,0),0)

[#6]

This did it. =IF(F12>0,ROUND(I12/F12,0),0)
Thank you!!