I have a doozy! I have been asked to breakout an R2 calculation...well,
when I did and "put" the numbers back together. They do not match...
The numbers a
Original R2 #: 0.293. The breakouts a 0.311, 0.284, 0.0857 which
when "put" back together comes up to: 0.234. The instantances for each
of these numbers a 126, 127, 106 respectively. The difference
between the original R2 and the "put" together is 0.058519. I have used
sumproduct, average, sum etc...but I cannot match the numbers up...

Anyone have any ideas on this and how to make equal??


Thanks,

Hans

Hans -

What is "an R2 calculation"?

What is "breakout"?

What is "put the numbers back together"?

- Mike
www.mikemiddleton.com

wrote in message
oups.com...
I have a doozy! I have been asked to breakout an R2 calculation...well,
when I did and "put" the numbers back together. They do not match...
The numbers a
Original R2 #: 0.293. The breakouts a 0.311, 0.284, 0.0857 which
when "put" back together comes up to: 0.234. The instantances for each
of these numbers a 126, 127, 106 respectively. The difference
between the original R2 and the "put" together is 0.058519. I have used
sumproduct, average, sum etc...but I cannot match the numbers up...

Anyone have any ideas on this and how to make equal??


Thanks,

Hans

Mike,

It may help if I call it the exact way Microsoft states in help...RSQ.
We have been using R2, so that's what I called it...Here is a more
specific outline of what I am trying to do.

For our Company we have a RSQ number (for today, the numbers change
daily) of 0.4074.
We have 3 regions that they would like their regional number
(breakout).
When I run the RSQ for each of these regions I get RSQ numbers:
..1529 in cell X6
..3475 in cell X7
..4167 in cell X8
For the following these are the store counts for each region
126 in cell Y6
127 in cell Y7
106 in cell Y8
Now when calculate these numbers back together (put the numbers back
together) I get a combined RSQ of .300
There is a .10077 difference between these.
My question is this: Why is there a difference? The formula that I used
to get this was:
SUMPRODUCT((X6:X8)*(Y6:Y8)/SUM(Y6:Y8))

Can you shed any light for me?


Thanks,

Hans

There is no reason that the weighted average of within group squared
correlations should equal the total squared correlation ignoring groups.

Jerry

" wrote:

Mike,

It may help if I call it the exact way Microsoft states in help...RSQ.
We have been using R2, so that's what I called it...Here is a more
specific outline of what I am trying to do.

For our Company we have a RSQ number (for today, the numbers change
daily) of 0.4074.
We have 3 regions that they would like their regional number
(breakout).
When I run the RSQ for each of these regions I get RSQ numbers:
..1529 in cell X6
..3475 in cell X7
..4167 in cell X8
For the following these are the store counts for each region
126 in cell Y6
127 in cell Y7
106 in cell Y8
Now when calculate these numbers back together (put the numbers back
together) I get a combined RSQ of .300
There is a .10077 difference between these.
My question is this: Why is there a difference? The formula that I used
to get this was:
SUMPRODUCT((X6:X8)*(Y6:Y8)/SUM(Y6:Y8))

Can you shed any light for me?


Thanks,

Hans

Jerry,

If I am understanding you correctly, these RSQ numbers will never
match? If this is true then, why? I am totally new to RSQ and do not
really understand it.

Could you shed some light as to why they would not match. And, if all
formulas are correct, then each "group" RSQ qould be correct...is that
right?


Thanks,


Hans

wrote:
If I am understanding you correctly, these RSQ numbers
will never match?

I would never say "never" when talking about combinations of
numbers. But the point is: there no mathematical reason to
expect them to match.

If this is true then, why?

One need only look at the mathematical formulas -- the one on
the RSQ help page and yours -- to see that there is no reason
to expect them to match, at least not without an elaborate proof.
(IMHO, it is "obvious" that they would not normally match.)

I am totally new to RSQ and do not really understand it.

And I suspect that math is not "your thing". So let me offer a
practical example, which might even match what you are trying
to accomplish.

And, if all formulas are correct, then each "group" RSQ qould
be correct...is that right?

Yes. But this is an example where "the whole might not equal
the sum of its parts".

RSQ is a measure of correlation between two sets of data -- for
example, actual sales v. target sales in each of several weeks.
RSQ is a value between 0 and 1. It is not an average deviation.

It is possible for each group of stores to have a very poor
correlation to their individual targets, but for all stores taken
together to have a close correlation to the combined target.
This would not be evident by simply computing a weighted sum
of the individual RSQ values.

Consider this example. Suppose you want to look at the
correlation over 52 weeks of the actual and target sales for two
regions, each with the same number of stores. Suppose that
for each week, the regions have exactly offsetting differences
from their targets. For example, if one region is 100 higher than
target, the other region is 100 lower.

Using random values, the RSQ for each region is less than one.

When the regional results are combined, they match their
combined target exactly. Thus, the combined RSQ is 1. But
your formula would compute the average of the individual RSQs
-- something less than one.

HTH.

Joe,

Thanks for the RSQ lesson! and yes; "math is not my thing". I will read
and re-read your explanation so I can explain to the people who want
the report. I appreciate the time and effort you put into the
explanation.

Thanks again!

Hans

" wrote:

One need only look at the mathematical formulas -- the one on
the RSQ help page and yours -- to see that there is no reason
to expect them to match, at least not without an elaborate proof.
(IMHO, it is "obvious" that they would not normally match.)
....
Yes. But this is an example where "the whole might not equal
the sum of its parts".

RSQ is a measure of correlation between two sets of data -- for
example, actual sales v. target sales in each of several weeks.
RSQ is a value between 0 and 1. It is not an average deviation.

Exactly

A simple example where overall RSQ obviously need not equal the weighted
average of subgroup RSQ's is the case where each subgroup has only 2
non-identical observations (x,y pairs). In that case, RSQ=1 for each
subgroup, but there is no necessary requirement that the overall RSQ of the
six observations will also be one.

More generally, if we define
M = AVERAGE(data)
SS = DEVSQ(data)
SC = COUNT(data)*COVAR(xdata,ydata)
then
RSQ(xdata,ydata) = SC(xdata,ydata)^2/(SS(xdata)*SS(ydata))

If we have three subgroups, then with
M = M1*n1/n +M2*n2/n +M3*n3/n
SSx = (SSx1+SSx2+SSx3 +n1*(Mx1-Mx)^2 +n2*(Mx2-Mx)^2 +n3*(Mx3-Mx)^2
SSy = (SSy1+SSy2+SSy3 +n1*(My1-My)^2 +n2*(My2-My)^2 +n3*(My3-My)^2
SC = (SC1+SC2+SC3 +n1*(Mx1-Mx)*(My1-My) +n2*(Mx2-Mx)*(My2-My)
+n3*(Mx3-Mx)*(My3-My)

If we greatly simplify by assuming that n1=n2=n3, Mx1=Mx2=Mx3 and
My1=My2=My3, then the overall RSQ reduces to
RSQ = (SC1+SC2+SC3)^2/((SSx1+SSx2+SSx3)*(SSx1+SSx2+SSx3))
which still need not equal
AVERAGE( SC1^2/(SSx1*SSy1), SC2^2/(SSx2*SSy2), SC3^2/(SSx3*SSy3) )

Jerry