Incorrect calculations

**NeilB84** · 03-03-2009, 12:16 PM

It been a few years since I've used Excel properly so I'm a bit rusty at it.

I'm currently having problems with some calculations for a project I've been given from work regarding percentages.

As can be seen from the attachment, the sheet calculates the departments participation by looking at what the department has took in relation to the whole company. The problem that I'm having is then using that percent figure to find out the target (8.04% of £1,140,763.98) as to my calculations
cell D11 should be £9,717.42 rather then £91,713.66

Here is the formula I'm using with the participation field formatted to percentage.

=SUM(C8*B11)

Any ideas as to where I'm going wrong?

**DonkeyOte** · 03-03-2009, 12:23 PM

Your % may be displayed to show % to 2 decimals but it seems the underlying value is actually 8.0397%

You could either adjust C11 such that it calcs to 4 decimals (ie 8.04%) or use ROUND in your D11 formula:

=$D$8*ROUND($C11,4)

**NeilB84** · 03-03-2009, 01:10 PM

Just tried this -

=$C$8*ROUND($B21,4)

And it is still coming up with the same incorrect answer.

Tried formatting to 4 decimal points with the old and new formula with success either.

**DonkeyOte** · 03-03-2009, 01:12 PM

The references you're using and your screen shot don't tie out so it's hard to comment - I would suggest you post a sample of your file with dummy data & the exact formulae you're using. I'm not a big fan of guessing.

**squiggler47** · 03-03-2009, 01:20 PM

shouldnt it be :-
=$C$8*ROUND($B21,2)

**DonkeyOte** · 03-03-2009, 01:22 PM

Assuming the % value is indeed entered as a % then 8.04% = 0.0804

**NeilB84** · 03-04-2009, 10:07 AM

Just tried =$C$8*ROUND($B21,2) and it gives me £91,261.12 rather then £91,713.66.

I've attached the worksheet to this reply as you requested.

**DonkeyOte** · 03-04-2009, 10:46 AM

I'm not sure why my earlier suggestion does not work for you, using your revised layout:

D21: =$C$8*ROUND($B21,4) --> 91,717.42

As discussed B21 although displaying as 8.04% is really more along the lines of: 0.080396703658977

So either use the above (D21) or alter your formula in B21 to =ROUND(C21/C9,4) ... then you can leave D21 as before.

**squiggler47** · 03-04-2009, 11:12 AM

Formula worked in the sheet as expected giving the correct result!

Not sure where you went wrong?

but as a side note, i'm not sure why you would want to round, other than to make your figures match the hypothetical calculation! If at a later time you tried to add up your figures you could be as much as £1000 out in your end total!