# Formula not calculating correctly

1. ## Formula not calculating correctly

I have a formula =B37*12*B28*5% where B37=\$18,000 and B28 = 0.233, which is derived from two other cells B27+B23 (.13+.10). The answer I am getting is 25.20 but when I calculate manually I get 25.16. Also, is I replace B28 with 0.233 rather than cell reference, I get 25.16.

2. ## Re: Formula not calculating correctly

Try this.

=B37*12*B28*.05

Use the decimal number intead of the % for the multiplication.

Simeon

3. ## Re: Formula not calculating correctly

I'm guessing your values in B27 and B23 have more decimal precision than you're showing.

First, 18000*12*0.233*5% = 2516.4 (not 25.16)

Second, expand your cells to show all decimal places. If I adjust B23 to 0.1003 and B27 to 0.133 they appear as 0.10 and 0.13, and the sum in B28 appears as 0.23 or 0.233 if I only go to 3 decimal places, but it's actually 0.2333, which will throw off your calculations a bit.

Excel does math on the actual value in the cell, not how it is displayed. There is a setting to calculate as displayed, but I'd recommend against ever using it.

4. ## Re: Formula not calculating correctly

My apologies. Formula is formula =B37*12*B28%*5%. I tried changint 5% to .5 and still get the same answer. If I expand, I get 35.20000000. I did try the option to calculate as displayed but that did not work. I have also tried increasing the iterative calculation and the multi-threaded calculation. Still, the same...

Thank you all for your responses!

5. ## Re: Formula not calculating correctly

Try posting a copy of your workbook. You can remove any sensitive data (as long as it won't impact the formulas in the cells mentioned). You can always use fake data, but remember to keep it of the same type (numbers where numbers should be, text for text, etc).

6. ## Re: Formula not calculating correctly

@ simeon, it makes no difference if you use 5% or 0.05 inteh calc, excel treatrs them as the same

7. ## Re: Formula not calculating correctly

Oh, I did not know that. I guess I could have saved myself some decimal places in past workbooks.
Thanks for the info!

8. ## Re: Formula not calculating correctly

lol now if i could only get it to do a spell check for me in here, i would be all set

10. ## Re: Formula not calculating correctly

I did an evaluation of the formula and it came up with "The cell currently being evaluated contains a constant". How can I fix that? Thank you all for your help!

11. ## Re: Formula not calculating correctly

That message is for evaluating a cell that contains a constant rather than a formula. You fix it by either selecting a cell that contains a formula before calling the evaluate tool, or putting a formula in the cell that is selected. Double check which cell you have selected before calling the evaluate formula tool.

And it looks to me like Paul explained the error -- your hand calculation is using rounded values, the Excel calculation is using the values to their full 15 digit precision. Ultimately this traces back to the B7 calculation that results in 4/30 which is a repeating decimal. Whether the "rounded" calculation like you did by hand or the full precision calculation as Excel did it is more correct, I will leave to you to decide. When you want Excel to use rounded numbers in calculations, be sure to include the ROUND() or similar function to force Excel to round to the correct number of decimal places.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1