Just a brain teaser (for the math guys)

[greenusmarine53]

Okay, so I've managed, with the help of those here, to make a pretty
impressive spreadsheet. But I ran into an interesting problem and maybe you
guys can help me understand it.

Just so you know, I've figured out another way to get my answer though I'm
not 100% sure it's the correct answer, this isn’t a “Help Me Please!!!â€
question. Just a ‘hmmm, that’s weird’ question.

I have a list of 35 annual training requirements. The title of each
requirement has its own column. The very first column has a list of about 230
names and each row should have the date in which that person completed the
requirement. Well, not every person has every requirement so I need a
percentage of how many people have completed any given requirement, by
section and total.

I did this by “=COUNTA(H3:H21)†at the bottom of each column and then below
that “= H22/ 19†as an example. 19 names divided by how many dates I have in
that column, or visa versa. Something’s divided by something; I’m too tired
to think.

Okay, because I have 11 different work sections, I have 11 different
worksheets, each one with 35 columns for the requirements. Now it used to be
that I had all the names together in one long list, but I figured it would be
easier to separate them by work section. Boy was I wrong! But it’s been fun
learning all this so it’s no biggy.

Anyway, I have 11 different “Operational Risk Management†(ORM) columns,
each with its own percentage of who’s completed the training. But I also
needed to know a total percentage. Each of those columns will tell me the
percentage for that section, just for those 7 to 20 people, depending on the
section. But I also need to know what that percentage is for everyone
combined.

Now I thought that I could use this fancy function,
“=SUM((ACAD!AY7+ADMIN!AY23+'S-3 OPS'!AY14+FACM!AY29+MESS!AY30+'S-4
LOGS'!AY10+MT!AY21+SUPP!AY23+FS!AY43+COMM!AY16+UTG!AY54)/ 11)â€

Well the AY is the column for ORM, the numbers are different because the
number of people are different for each section. Now here’s the important
thing to remember, AY7, AY23, AY14 and so on, those correspond to the row
that tallies the percentage for each section. You’ll notice that I divide all
that by 11, which gives the total for everyone.

Well, you have your answer, you might say. BUT…

When I went back and did it manually by numbers alone, in other words, just
to check the math, I counted all of the people who completed the requirement
and then divided that by 230 and I got a different number, a different
percentage. That’s what this is all about. Why am I getting a different
number?

Whatever the answer, I decided I trusted the numbers alone approach more and
redid all my equations or whatever they’re called. But I was wondering if a
math genius here might tell why the answers are different. My thought was
that the percentages by section were conflicting because each section has a
different number of people, if that makes any sense. The odd thing is, there
is only a few points difference between the two different answers, in some
cases about .05 and at most 6.

I know that this is a bit wordy but I wanted to paint a clear picture AND I
know that this is more of a math question and has less to do with Excel. But,
in my defense, it started because of Excel, so there.

Thank you much for your time. Hopefully you’ll consider this a break from
the normal questions.

 

[JLatham]

Give us those differences again, in same format. You said ".05 and 6" did
you mean that, or should the 6 have perhaps been .06??

If that's the case it may just be a situation where Excel's internal
calculations are a bit off due to the inability of a binary machine to
exactly represent decimal numbers in every case. Sometimes adding what
appear to be the same group of numbers in a column and checking that against
the sum of those columns by adding them as a row actually comes up with 2
slightly different numbers.

Here's a kind of example. In cells A1:A3 put the formula =1/3 each one
will display as 0.333333. In A4 put =SUM(A1:A3) It will most likely display
1 which is what you think it should be seeing as how you know how you got the
0.333333 in A1:A3. But then in another cell put = .333333+.333333+.333333
and the result will be 0.999999, not 1. So even though it looks like you're
adding the same values, internally to Excel, you aren't.

So if your differences are only like .05 or .06, then I wouldn't give it a
second thought - close enough for government work!

(JLatham, Excel MVP ... and MSgt, USAF (Ret)) - Keep up the good work!

 

[JoeU2004]

Anyway, I have 11 different “Operational Risk Management†(ORM) columns,
each with its own percentage of who’s completed the training. But I also
needed to know a total percentage.
[....]
Now I thought that I could use this fancy function,
“=SUM((ACAD!AY7+ADMIN!AY23+'S-3 OPS'!AY14+FACM!AY29+MESS!AY30+'S-4
LOGS'!AY10+MT!AY21+SUPP!AY23+FS!AY43+COMM!AY16+UTG!AY54)/ 11)â€
[....]
When I went back and did it manually by numbers alone, in other words,
just
to check the math, I counted all of the people who completed the
requirement
and then divided that by 230 and I got a different number, a different
percentage. That’s what this is all about. Why am I getting a different
number?

If I understand things correctly, I believe you have committed a
mathematical error in your Excel formulation.

Permit me to abstact your problem somewhat. I hope this will clarify
things. Please let me know.

For each of the 11 work sections, you have Yn names, Xn of which have
completed the ORM requirement. So the percentage for each work section is
X1/Y1, X2/Y2,..., X11/Y11.

The correct total percentage is: (X1+X2+...+X11) / (Y1+Y2+...+Y11). That
is what you calculated manually.

But your Excel formula is computing (X1/Y1 + X2/Y2 + ... + X11/Y11) / 11.

Those two formulas are equivalent when all Yn are the same.

But they are not the same when some Yn are different, which you said is the
case.

Consider this hypothetical example. One employee in each work section
completes the requirement; so all Xn are 1. Consider two cases:

Case 1: The number of employees in each work section is 2. So 50% of the
employees in each work section completed the requirement. Your formula
would add 50% 11 times (11*50%), then divide by 11; the result is 50%. And
indeed 11 out of 22 employees completed the requirement; a total of 50%.

Case 2: The number of employees is 1 in work section 1, 2 in section 2, 3
in section, etc up to 11 in section 11. So the individual percentages are
1/1, 1/2, 1/3, 1/4, 1/5, 1/6, 1/7, 1/8, 1/9, 1/10 and 1/11. Your formula
would add all those -- about 3.019877 -- and divide by 11; the result is
about 27.45%. But the actual total percentage is 11 divided by 66
(1+2+...+11); the result is about 16.67%.


----- original message -----

 

[greenusmarine53]

To the first, the differences were between .05% and 6%.

To both, thank you. I figured it had something to do with the way I was
dividing the numbers. And thank you for confirming that I was right to go
with a simple numbers solution rather than messing with the percentages.