Can anyone suggest a way out of this dilemma?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nick Xylas - 05 Nov 2007 17:57 GMT

I work for a state government agency which is currently working on its
annual report, mostly consisting of Excel tables of how well other
state agencies are doing in meeting their Equal Employment Opportunity
(EEO) targets. I posted a few days ago for advice on how to round
figures, because the final percentage figures were being calculated on
unrounded figures while the figures displayed in the table are rounded
to a single decimal place, meaning that agencies were calling us to
complain that our percentages didn't match their calculations. Someone
very kindly showed me the way to do this, so that the overall
percentage matched the percentage that you would get if a third pary
went through the printed figures using a calculator, but now I'm
finding that the total percentage in each EEO category, which should
add up to 100%, are actually adding up to anywhere between 90.3 and
103.8%, because it is adding up the rounded percentages. I seem to be
caught between a rock and a hard place. Has anyone come across a
similar situation before, and is there a way out?

Reply to this Message

Bernie Deitrick - 05 Nov 2007 18:53 GMT

Nick,

I have to think that you are calculating your percentages incorrectly. On average, you will get as
many values rounded up as you get rounded down, and then by only tenths of a percent at a time, so I
have trouble imagining a realistic scenario where your sum is off by the amount you describe.

Describe your set up better, and (Even better) post an example of the numbers that you have - you
don't have to post what the numbers mean - and we'll take a look from there.

HTH,
Bernie
MS Excel MVP

>I work for a state government agency which is currently working on its
> annual report, mostly consisting of Excel tables of how well other
[quoted text clipped - 12 lines]
> caught between a rock and a hard place. Has anyone come across a
> similar situation before, and is there a way out?

Reply to this Message

Nick Xylas - 05 Nov 2007 20:47 GMT

On Nov 5, 1:53 pm, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:

> Describe your set up better, and (Even better) post an example of the numbers that you have - you
> don't have to post what the numbers mean - and we'll take a look from there.

OK, I'll do my best. It'd be so much easier if we could attach
spreadsheets to usenet posts!

The figures are in columns D thru I. They come in sets of two rows. In
each category, the top row contains numbers of people, while the
bottom row displays the percentage of the total that each column
represents. Column J is the total.

The formula in the percentage column is as follows. The example is
from cell D8, with other cells adjusting their cell references
accordingly:

=IF(J7>0,ROUND(D7/J7*100,1),"")

D7 is the cell with the total for that row, formula =SUM(D7:I7)

D8 totals the percentages, and should add up to 100%,
formula=ROUND(SUM(D8:I8),1)

Here are the actual numbers in cells D8 thru I8: 1,1,0,2,1,0. Cell J8
does actually equal 100% and J10=99%, which is within the margin of
error that one would expect. But when I do the same thing in row 11,
things get weird. The figures are as follows: 28,1,2,12,3,1. The
percentages in row 12 are 59.6%, 2.1%, 0.0%, 30.0%, 10.0%, 2.1% adding
up to 103.8%

Next row, row 13: 34, 1 ,4, 15, 2, 1
Percentages in row 14: 59.6%, 1.8%, 0.0%, 30.0%, 0.0%, 1.8% totalling
93.2%

Figures in row 15: 39, 6, 1, 8, 2, 0
Percentages in row 16: 69.6%, 10.7%, 0.0%, 10.0%, 5.0%, 0.0% totalling
95.3%

Even just looking at this, I can see that some of the figures are off,
but I can't see where the error in the formula lies.

Reply to this Message

Bernie Deitrick - 05 Nov 2007 21:06 GMT

Nick,

Let's take a look at the numbers in row 15:

> Figures in row 15: 39, 6, 1, 8, 2, 0

In cell J15:
=SUM(D15:I15)

Copy to cell J16.

In cell D16:
=IF($J15>0,ROUND(D15/$J15,3),"")

Format Cell D16 for Percent, 1 decimal, and then copy to E16:I16

You got this:

> Percentages in row 16: 69.6%, 10.7%, 0.0%, 10.0%, 5.0%, 0.0% totalling
> 95.3%

but you should now get

69.6% 10.7% 1.8% 14.3% 3.6% 0.0%

Which will add up to 100.0%

HTH,
Bernie
MS Excel MVP

Reply to this Message

Sandy Mann - 05 Nov 2007 21:10 GMT

I get 100 for 28,1,2,12,3,1 when I change the formula to absolute columns:

=IF($J11>0,ROUND(D11/$J11*100,1),"")

and 100.1 for 13.34, 1 ,4, 15, 2, 1 again with absolute columns in the
formula.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> On Nov 5, 1:53 pm, "Bernie Deitrick" <deitbe @ consumer dot org>
> wrote:
[quoted text clipped - 39 lines]
> Even just looking at this, I can see that some of the figures are off,
> but I can't see where the error in the formula lies.

Reply to this Message

Nick Xylas - 06 Nov 2007 14:50 GMT

> I get 100 for 28,1,2,12,3,1 when I change the formula to absolute columns:
>
> =IF($J11>0,ROUND(D11/$J11*100,1),"")
>
> and 100.1 for 13.34, 1 ,4, 15, 2, 1 again with absolute columns in the
> formula.

That worked, thanks.

Reply to this Message

Rate this thread: