RSQ function

Thanks Bob:

I flick-passed that to someone who will actually understand the question

I'll get back to you if we get an answer.

Cheers


On 5/11/09 10:07 PM, in article C7191934.B68E%b_jordan@xtra.co.nz, "Bob
Jordan" <b_jordan DeleteThis @xtra.co.nz> wrote:

> No I run 2004 and it pops up occasionally and I have now located a number of
> other people who have seen it. They can be found using refs in my previous
> posting.
>
> The one that stimulated this post was earlier (I think) in this thread
> showing almost exactly the same thing as I have experienced but in Excel
> 2008.
>
> RSQ does not seem to fail very often so there is something I do not
> understand.
>
> I just tried it then and managed to make it fail.
>
> Note this is a first time run of Excel after a clean install last night.
>
> This is not elegant but try it in 2004 (Note I have 11.5.5 running on a
> 10.4.11 Tiger powerPC iMac
>
> In B5-9 put 1,2,3 and 4 i.e. the x values
>
> In C2-4 put 1,2,3
>
> In C5 put equation =$C$2*B5+$C$3*(B5-$C$4)^2 and propogate down to C9
> That is a quadratic which will be most linear when C3=0
>
> In cell c11 put =RSQ(B5:B8,C5:C
>
> For test purposes in cell D11 put =RSQ({0,1},{1,0})
> That should always have value 1 but is a perfectly negative correlation
>
> Now run solver to set cell c11 to a maximum by adjusting cells C2-4
>
> It will fiddle with C2 and C4 and may get any value but C3 will come out
> very small. The RSQ is essentially 1 - correct.
>
> But look in cell d11 - it shows -1 that is impossible!
>
> Now set c2-4 back to 1,2,3
>
> The RSQ value goes to -0.48795 or something like it
>
> A negative R2 value - what the?
>
> Now using RSQ and solver is valid to find a quick model fit (if care taken)
>
> But not if it screws up the RSQ function from then on.
>
> I have also seen it in a graph where a -ve R2 is shown on a trendline.
>
> I would love someone to say what this is and how to avoid it.
>
> I have put the file in my drop box and it can be accessed using the link
>
> http://dl.getdropbox.com/u/2835140/RScrewed.xls
>
> If you just load it you will see the -ve values.
>
> But enter the sheet data the long way first.
>
> Take care in interpreting what happens - Excel only recomputes a line if
> something has changed. If you edit the RSQ line or one of the values it will
> become correct.
>
> But the problem goes away sometimes - it is crazy to try and find a pattern.
>
>
> On 11/5/09 10:35 PM, in article C718E797.3AC9%john@mcghie.name, "John
> McGhie" <john DeleteThis @mcghie.name> wrote:
>
>> Hi Bob:
>>
>> I can't produce the fault here, in either Excel 2008 or the next version.
>>
>> Do you have a more extensive test we can try?
>>
>> Cheers
>>
>


--

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:john@mcghie.name