Excell Formula is miscalculating based on other cells.

[sibley14]

Hi, looking for a bit of advice.

i have a built spreadsheet where i am calculating the cost of a product based on other factors. To give an example:

A1) 2.21 >>>>entered manually
A2)=A1 >>>> (so shows 2.21)
A3)=ROUND(SUM(35/B11),2) >>>> to explain, this is taking a figure of 35, then dividing it by the number of items in Cell B11, giving a result of 0.01 rounded to 2 DP
A4)=A2*I4 >>>>Here this is taking 2.21 and adding 5% which actually results in 2.33
A5)=SUM(A2:A4) >>>>This gives me a result of 2.33
A6)=SUM(A5*B9*B8) >>>>this is taking my total in A5, then x it by a width in B9 (1.000) and a length in B8 (250) giving me a result of 582.63.

However, the result should be 2.33 x 1.000 x 250 = 582.50.

it's only a difference of 0.13 i realised but over various spreadsheets the difference is bigger based on whether the width or length changes.

My gut feeling is that this is something to do with decimal places and how excell is calculating things in the background, vs what i get on a calculator - that or my formulas are wrong.

Thanks in advance.

 

[Joe4]

A few things.

You haven't told us what is in cell B11, which is vital to your calculation.

Also, the SUM function serves no purpose in these formulas. You can get rid of it.
=ROUND(SUM(35/B11),2)
=SUM(A5*B9*B8)
so you just have:
=ROUND(35/B11,2)
=A5*B9*B8

The SUM function is used to sum up multiple cells at once, like when used there:
=SUM(A2:A4)
There is no point in using it on a single value (calculated or not).

 

[sibley14]

Thanks for pointing that out. i've now been through my spreadsheet with a very fine toothcomb and i discovered that it was simply a case of adding the =Round formula to a cell that was calculating multiplying 3 other cells together. Without the =Round function is was calculating the whole of the decimal equation, instead of using the rounded up figure to 2 DP. This is why my calculation was off.

Really appreciate your help on this and giving me the impetus to go through my formulas and double check them.

And if you know how to default excel spreadsheets to 2 DP then please share as that would be useful!

 

[Joe4]

And if you know how to default excel spreadsheets to 2 DP then please share as that would be useful!

Note that you can create your own Excel templates that are pre-formatted any way you like.
See: Templates in Excel

Also note that there is a "precision as displayed" option in Excel, which, if you set it, will drop all decimal places not being shown.
Just note that this may have some unintended consequences though. See: Excel: The dangers of “Set precision as displayed”

 

[sibley14]

Fantastic, thanks @Joe4 - super helpful.