Percentage calculations are incorrect

[nikkiloued]

Hi there,

This is probably a really basic fix but it from a spreadsheet my boss created so I want to be sure before I mess with it.
We are creating an order sheet for garments.

First step:
We have headcounts per item, we then add a 30% redundancy to calculate the minimum amount to order.
Formula used:
=I470*(1+30%)
I470 being the headcount.
Cell showing the headcount is formatted to show a number to 0 decimal places.

Second step:
These orders need to be divided per gender percentage.
So it is set up to have column M set as a percentage and we input the divide so say 40% female.
The contents of cells in column K is the minimum order inclusive of the 30% as calculated in the above formula
=K470*M470
Column K is formatted as number to 0 decimal places
Column M is formatted as percentage to 0 decimal places
The cell that shows the result is formatted to number to 0 decimal places
as you can imagine this is where I can see discrepancies with the totals not adding up to the whole order

Third Step:
From these separated totals per gender we then need to calculate the cost of the order.
So a simple Cell*Cell formula has been inputed.
But I think because the source cell is off it is showing a mis-calculation.
For example it is calculating that 16 x 35 as 546 NOT 560 as it should be.

I think that this is to do with not rounding but I am not sure how to correct the formulas as everything I know seems to be showing errors.
This is not the kind of thing I am familiar with so I want some expertise before I meddle. I am more used to working in very basic calculations and I am learning as I go filling in for another colleague in an emergency.

Your help would be very much appreciated...

Thanks

Nikki

 

[Joe4]

Yes, you are correct that the issue is with rounding.

The important thing to understand is that changing the formatting only changes what is shown/presented on the screen. It does NOT change the underlying value.
If you want to change the stored underlying value, you will need to use the ROUND formula, and be sure that the ROUND formula rounds to the same number of places you are showing.

So you would write your original formula of:

[COLOR=#333333]=I470*(1+30%)[/COLOR]

like this:

[COLOR=#333333]=[/COLOR][COLOR=#ff0000]ROUND([/COLOR][COLOR=#333333]I470*(1+30%)[/COLOR][COLOR=#ff0000],0)[/COLOR][COLOR=#333333]
[/COLOR]

Also note this. Even with rounding, your values may not add up to the original amount!

Here is a simple example. Divide 10 equal three ways, rounding off to the nearest whole number (0 decimals).
That would be 3, 3, and 3. Sum those up and you get 9, which is different that your original value of 10.

Even if you rounded off to two decimals, you would have 3.33, 3.33, and 3.33, which adds up to 9.99.

If it is really important to have it sum up to the original amount, people may adjust the last amount (take the original amount and subtract the first two, i.e.:
=10 - 3.33 -3.33 = 3.34

 

[nikkiloued]

Thanks Joe,

With the second I should add a round also?
So would I apply the same formula?
=ROUND(K470*M470),0)

Or is applying the first rounding enough to carry it over?

I know this is probably totally basic but it is not something I am familiar with.

Thanks

 

[Joe4]

If F470 and M470 are already rounded to the nearest whole number (no decimals), then you would not need to round their product.
If they are not, you would want to round them as you have shown.

Note that there is no harm in rounding them. So if you are unsure or just want to be safe, go ahead and use ROUND on all your calculations.

 

[nikkiloued]

Thank you, it all works now!
I love this forum!!
Forgive me if I bounce back with more questions! I am covering this for at least another month!!

Nikki

 

[Joe4]

You are welcome!

Forgive me if I bounce back with more questions!

No need to ask for forgiveness, that is what we are here for, so feel free to ask away!