All,

I am using sumproduct to calculate percentages on 3 different levels.
One is a divisional level another Regional and last district....An
example is 83.5% for divisional, 77.3% region 1, 83.2% region 2, 90.8%
region 3. When I average the three regions together I do not arrive at
83.5%; I have 83.8%. This occurs between the district and regional
levels also, and I am perplexed!

All of my counts are correct on each level and I am utilizing the same
exact calculations at each level. Why will the averages be
different???? Anyone have a clue?

Thanks,

Hans

Sounds like rounding in some cases and not in others. What are the formulae?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
oups.com...
All,

I am using sumproduct to calculate percentages on 3 different levels.
One is a divisional level another Regional and last district....An
example is 83.5% for divisional, 77.3% region 1, 83.2% region 2, 90.8%
region 3. When I average the three regions together I do not arrive at
83.5%; I have 83.8%. This occurs between the district and regional
levels also, and I am perplexed!

All of my counts are correct on each level and I am utilizing the same
exact calculations at each level. Why will the averages be
different???? Anyone have a clue?

Thanks,

Hans

Bob,

Here is the formula;
=SUMPRODUCT((($A$35:$A$413=$A6)*(D$35:D$413=$B$7))/$C6)+SUMPRODUCT((($A$35:$A$413=$A6)*(D$35:D$413=$B $6))/$C6)

Column A is where the managers name is located
Cell A6 is the managers name
The 1st section of column D is the count for "yes" answers
The 2nd section of column D is the count for "no" answers
C6 is the store count under that manger

I'm no pro at SumProduct so am not much use there.

But it seems to me that your "Division" average (83.5) is an average for ALL
stores, managers, data, etc.

Your average of the three region's averages is 83.8. This average does not
take into account weighting due to size of region (amount of stores, etc.).

HTH

" wrote:

All,

I am using sumproduct to calculate percentages on 3 different levels.
One is a divisional level another Regional and last district....An
example is 83.5% for divisional, 77.3% region 1, 83.2% region 2, 90.8%
region 3. When I average the three regions together I do not arrive at
83.5%; I have 83.8%. This occurs between the district and regional
levels also, and I am perplexed!

All of my counts are correct on each level and I am utilizing the same
exact calculations at each level. Why will the averages be
different???? Anyone have a clue?

Thanks,

Hans

That is my problem! I am NOT a sumproduct pro also... I placed out the
decimal point to 30 and it just does not come out correct. To my
knowledge there is not a mathematical issue here, so it must be in
Excel or most likely just in sumproduct. Does anyone else out there
have a clue as to why this happens???

Dominic thanks for your help though!


Hans

I don't think you're interpreting Dominic correctly.

If your regions have different numbers of items, then the average of all
the items will not normally be the average of the region's average. For
instance:


Region 1: 100, 40, 60, 20 == Average: 55
Region 2: 70, 80 == Average: 75
Region 3: 60, 60, 60 == Average: 60

The average of all items (100, 40, 60, 20, 70, 80, 60, 60, 60) is 60.5.

However the grand average, or the average of the averages (55, 75, 60)
is 61.11111111

To get the averages to total to the grand average:

=((4 * 55) + (2 * 75) + (3 * 60))/9 == 61.11111111

If that's not the issue, it may be a difference due to rounding - most
numbers can't be represented exactly in a finite number of binary
digits. See

http://cpearson.com/excel/rounding.htm

or

http://www.mcgimpsey.com/excel/pennyoff.html

but 0.3 seems like a rather large difference to be due to this.

In article .com,
" wrote:

That is my problem! I am NOT a sumproduct pro also... I placed out the
decimal point to 30 and it just does not come out correct. To my
knowledge there is not a mathematical issue here, so it must be in
Excel or most likely just in sumproduct. Does anyone else out there
have a clue as to why this happens???

Hans,

Perhaps you should post all four of your SumProduct formulas for the experts
to look at. The one for Division, and the three for Region.

Not that I think I have any idea what is going on, but are you sure my
suggestion is not the cause of your problem. For example:

Manager Region Data
A A 1
B A 1
C A 0
D B 1
E B 1
F C 1

Your Division average above is 83.33%

Region A average: 66.66%
Region B average: 100%
Region C average: 100%

Average of A,B,C regions: 88.67%

Is this the way your formulas are set up to calculate?

HTH

" wrote:

That is my problem! I am NOT a sumproduct pro also... I placed out the
decimal point to 30 and it just does not come out correct. To my
knowledge there is not a mathematical issue here, so it must be in
Excel or most likely just in sumproduct. Does anyone else out there
have a clue as to why this happens???

Dominic thanks for your help though!


Hans

Dominic was saying that the issue is not sumproduct, but your expectation,
which seems to defy basic math.

Your sumproduct formula apears to be fine and calculates c1/n1 for region 1,
where c1 is the total of yes's and no's for that region and n1 is the number
of stores. Your divisional percentage is then
(c1+c2+c3)/(n1+n2+n3)
Why do you think that should be equal to (c1/n1+c2/n2+c3/n3)/3 unless
n1=n2=n3?

Also note that Excel (like all software that follows the IEEE double
precision standard) is not capable of 30 figure accuracy, since double
precision can only represent 15-16 digits reliably (hence Excel's documented
limit of displaying no more than 15 digits).

Jerry

" wrote:

That is my problem! I am NOT a sumproduct pro also... I placed out the
decimal point to 30 and it just does not come out correct. To my
knowledge there is not a mathematical issue here, so it must be in
Excel or most likely just in sumproduct. Does anyone else out there
have a clue as to why this happens???

Dominic thanks for your help though!


Hans

Dominic....thanks for the input, but JE hit it on the head in a way I
understood, I DID not know that! You learn something new everyday....It
worked exactly as stated....Now, when I get questioned as why these are
not the same I can totally dazzle or most likely baffle 'em

Thanks

Hans

Hans,

Glad we could help.

Good luck with the dazzling

" wrote:

Dominic....thanks for the input, but JE hit it on the head in a way I
understood, I DID not know that! You learn something new everyday....It
worked exactly as stated....Now, when I get questioned as why these are
not the same I can totally dazzle or most likely baffle 'em

Thanks

Hans