RSQ function

Hi Mike,
I guess that would be the matrix that I am talking about.
I am using the same version, but get R when I ask for RSQ. I can easily get the same result by squaring everything, but that is not really the idea of Excel. I do get the correct R^2 when I plot a line of best fit.

It sounds like there is some issue then with my machine? I was getting the correct value before the update. I have tried it a number of times to make sure I am not doing anything really obviously daft, but my RSQ value consistently equals my CORREL value.

All very confusing. Recommendations? Remove and reload Excel and start again?

Reuben

Reuben or Southward -

> Recommendations? <

Try RSQ and CORREL with a very small data set. The "regression matrix" to
use with these functions is two arrays of equal size. For my tests, I used
two arrays, each with five values. See what happens. If you get CORREL = R
and RSQ = R^2, as I do with the simple example, then look carefully at the
setup for the incorrect results of your original "regression matrix."

> Remove and reload Excel and start again? <

From what I read in these newsgroups, reinstalling seldom solves problems
with Mac Excel.

- Mike

Web http://www.MikeMiddleton.com



<Southward DeleteThis @officeformac.com> wrote in message
news:59b79752.1@webcrossing.caR9absDaxw...
> Hi Mike,
> I guess that would be the matrix that I am talking about.
> I am using the same version, but get R when I ask for RSQ. I can easily
> get the same result by squaring everything, but that is not really the
> idea of Excel. I do get the correct R^2 when I plot a line of best fit.
>
> It sounds like there is some issue then with my machine? I was getting the
> correct value before the update. I have tried it a number of times to make
> sure I am not doing anything really obviously daft, but my RSQ value
> consistently equals my CORREL value.
>
> All very confusing. Recommendations? Remove and reload Excel and start
> again?
>
> Reuben

2 1 0.996783819
4 1.9 0.996783819
6 3.1
8 4.4

Hi Mike,
Above is a simple data set. The first value is the CORREL result, the second is the RSQ result.

Reuben

> Reuben or Southward -
>
> > Recommendations?
> Try RSQ and CORREL with a very small data set. The "regression matrix" to
> use with these functions is two arrays of equal size. For my tests, I used
> two arrays, each with five values. See what happens. If you get CORREL = R
> and RSQ = R^2, as I do with the simple example, then look carefully at the
> setup for the incorrect results of your original "regression matrix."
>
> > Remove and reload Excel and start again?
> From what I read in these newsgroups, reinstalling seldom solves problems
> with Mac Excel.
>
> - Mike
>
> Web <http://www.MikeMiddleton.com>
>
>
>
> wrote in message
> news:59b79752.1@webcrossing.caR9absDaxw...
> > Hi Mike,
> > I guess that would be the matrix that I am talking about.
> > I am using the same version, but get R when I ask for RSQ. I can easily
> > get the same result by squaring everything, but that is not really the
> > idea of Excel. I do get the correct R^2 when I plot a line of best fit.
> >
> > It sounds like there is some issue then with my machine? I was getting the
> > correct value before the update. I have tried it a number of times to make
> > sure I am not doing anything really obviously daft, but my RSQ value
> > consistently equals my CORREL value.
> >
> > All very confusing. Recommendations? Remove and reload Excel and start
> > again?
> >
> > Reuben
>
>

Reuben -

With your simple data set, I get CORREL = 0.996783819 and RSQ = 0.993577982.

Hopefully one of the Mac Excel MVP gurus will reply with a suggestion for
you.

If not, I suggest you verify your Excel version (by choosing Excel > About
Excel) and then post again with that information.

As I mentioned in a previous message of this thread, I am using "Mac Excel
2008 with 12.2.0 update."

- Mike

http://www.MikeMiddleton.com



<Southward RemoveThis @officeformac.com> wrote in message
news:59b79752.3@webcrossing.caR9absDaxw...
> 2 1 0.996783819
> 4 1.9 0.996783819
> 6 3.1
> 8 4.4
>
> Hi Mike,
> Above is a simple data set. The first value is the CORREL result, the
> second is the RSQ result.
>
> Reuben
>
>> Reuben or Southward -
>>
>> > Recommendations?
>> Try RSQ and CORREL with a very small data set. The "regression matrix" to
>> use with these functions is two arrays of equal size. For my tests, I
>> used
>> two arrays, each with five values. See what happens. If you get CORREL =
>> R
>> and RSQ = R^2, as I do with the simple example, then look carefully at
>> the
>> setup for the incorrect results of your original "regression matrix."
>>
>> > Remove and reload Excel and start again?
>> From what I read in these newsgroups, reinstalling seldom solves problems
>> with Mac Excel.
>>
>> - Mike
>>
>> Web <http://www.MikeMiddleton.com>
>>
>>
>>
>> wrote in message
>> news:59b79752.1@webcrossing.caR9absDaxw...
>> > Hi Mike,
>> > I guess that would be the matrix that I am talking about.
>> > I am using the same version, but get R when I ask for RSQ. I can easily
>> > get the same result by squaring everything, but that is not really the
>> > idea of Excel. I do get the correct R^2 when I plot a line of best fit.
>> >
>> > It sounds like there is some issue then with my machine? I was getting
>> > the
>> > correct value before the update. I have tried it a number of times to
>> > make
>> > sure I am not doing anything really obviously daft, but my RSQ value
>> > consistently equals my CORREL value.
>> >
>> > All very confusing. Recommendations? Remove and reload Excel and start
>> > again?
>> >
>> > Reuben
>>
>>

Hi Mike,
How curious. Yes, my version of Excel is 12.2.0 also.

Note also that when I try and put a function in using the Formula Builder, I am told that "Excel does not recognise this function", although it happily provides examples of how it might be used. It also says that "Help is not available for this function" when I click on that link - however, if I search in Help, there it is.

I also hope that it catches the eye of someone somewhere who may have an answer. I have only recently started using Office on the mac - previously I was going very happily with NeoOffice, but sadly that doesn't graph quite as well, nor integrate with the bibliography software I run. It never seemed quite so random in its actions as Excel does (or Word for that matter). All of which is neither here nor there as far getting this problem sorted, of course.

Your time is most appreciated, thanks.

Reuben

> Reuben -
>
> With your simple data set, I get CORREL = 0.996783819 and RSQ = 0.993577982.
>
> Hopefully one of the Mac Excel MVP gurus will reply with a suggestion for
> you.
>
> If not, I suggest you verify your Excel version (by choosing Excel > About
> Excel) and then post again with that information.
>
> As I mentioned in a previous message of this thread, I am using "Mac Excel
> 2008 with 12.2.0 update."
>
> - Mike
>

In article <59b79752.3.RemoveThis@webcrossing.caR9absDaxw>,
Southward.RemoveThis@officeformac.com wrote:

> 2 1 0.996783819
> 4 1.9 0.996783819
> 6 3.1
> 8 4.4
>
> Hi Mike,
> Above is a simple data set. The first value is the CORREL result, the second
> is the RSQ result.

I get

CORREL: 0.996783818915303
RSQ: 0.993577981651376

with XL04 and XL08.

Is calculation set to automatic (Preferences/Calculation)?

If you use Formula Builder, do you see the same results in the palette?

In article <59b79752.5 RemoveThis @webcrossing.caR9absDaxw>,
Southward RemoveThis @officeformac.com wrote:

> Note also that when I try and put a function in using the Formula Builder, I
> am told that "Excel does not recognise this function", although it happily
> provides examples of how it might be used. It also says that "Help is not
> available for this function" when I click on that link - however, if I search
> in Help, there it is.

Never seen that before - does it happen if you start XL with the Shift
Key down?

Hi,
For a moment there I was a very happy fellow! I checked that the calculation was set to automatic (it was).
Closed and quit everything, then fired up Excel with the shift key down, and everything was calculating exactly as it should be! Hurray! I was able to autofill across some regression tables that I had made, and - even though the formula did not change - the result became correct. That occurred even when the cell that I used to start the autofill drag from was giving the incorrect result. (I then dragged backwards over that cell, and the result changed to what it should be.)

BUT:
- Excel shut down then restarted (not sure why, but I had put a big file in the clipboard)
- I am back to the same problem.
I tried getting the formula going by typing it in, and also by using the formula builder, same result (I used the same numbers that are in the above post, and again my RSQ equals my CORREL).
I also opened a file that had been created in Excel 2007 and when it opened it was giving the incorrect result.
I shut down Excel again, reopened using the shift key. I opened the simple data set that I am using for this forum again - results were not correct, but when I retyped the formula in, it came up trumps. I closed that sheet and then reopened the same sheet, and an RSQ that I hadn't fiddled with recalculated correctly. (I had two -one from typing the formula in, one from the formula builder, as detailed above).
Opened the same file created in Excel 07 again, and results are now correct.

Opened another file, and was rather sad to see, however, that it all started to go pear shaped again.

After stuffing around as detailed above, I have started to link what I am seeing with that particular file. I had another version of the same data, so deleted the suspect file and replaced it with a good one. Lo and behold - all is working again. At the moment.

The Unrecognised Function message still pops up if I use the formula bar's formula builder button, however oddly it works fine when I use the formula builder from the toolbar - but I can live with that.

If there was a macro or equivalent that was causing the problem (I think this is what holding down shift does when opening), is there anywhere else I should be looking to remove it?

Thanks for your time helping me out. I am quite chuffed to be getting so much closer to sorting it all out!

Reuben

In article <59b79752.8.RemoveThis@webcrossing.caR9absDaxw>,
Southward.RemoveThis@officeformac.com wrote:

> If there was a macro or equivalent that was causing the problem (I think this
> is what holding down shift does when opening), is there anywhere else I
> should be looking to remove it?
>
> Thanks for your time helping me out. I am quite chuffed to be getting so much
> closer to sorting it all out!

Holding the shift key down bypasses your preferences (as well as macros,
but XL08 doesn't run them anyway). It also bypasses any customized
default template you may have set, and creates a new workbook with
factory settings.

It sounds like the XL07 file/template is corrupted. That may not show up
in XL07, since that version has more comprehensive error recovery
routines than XL08.

Just to put my pennies worth in on this.

I have reported before on a similar problem in Excel2004

I believe that in my case the Solver was involved

The test function I use is this =RSQ({0,1},{1,0})

It should give a value of +1 but if the fault occurs it gives -1 which
happens to be the correl result.

To explore the postings do a google search on negative rsq

I have also seen rsq come up negative on charts as well.

My gut feeling is that it is a fault in the return from the rsq and correl
functions. I assume they share code and just do the appropriate squaing if
needed on exit.

I know that Solver in 2004 would screw it up and one had to restart Excel to
clear the fault. It could be recreated by getting solver to maximise a cell
that computed the RSQ of two columns - seems a logical way of fitting
curves?

But I suspect that the fault is more general.

Unfortunately it is not seen often because most people work with positive R
values and who can tell the difference between RSQs of 0.996 and 0.993
anyway


Pat McMillan at the Macintosh Business Unit took a copy of the file that
generated the fault but there has been no progress on this apparent to me.


Bob J.

Once again I was stymied by replying to the Mactopia webpage rather than to
the newsgroup directly.

I posted this comment on the RSQ problem some time back

The negative RSQ problem sounds awfully similar to an Excel 2004 problem
that has been reported occasionally

Just to put my pennies worth in on this.

I believe that in my case that the Solver was involved

The test function I use is this =RSQ({0,1},{1,0})

It should give a value of +1 but if the fault occurs it gives -1 which
happens to be the correl result.

I have also seen rsq come up negative on charts as well.

My gut feeling is that it is a fault in the return from the rsq and correl
functions. I assume they share code and just do the appropriate squaring if
needed on exit.

I know that Solver in 2004 would screw it up and one had to restart Excel to
clear the fault. It could be recreated by getting solver to maximise a cell
that computed the RSQ of two columns - seems a logical way of fitting
curves?

But I suspect that the fault is more general.

Unfortunately it is not seen often because most people work with positive R
values and who can tell the difference between RSQs of 0.996 and 0.993
anyway


Pat McMillan at the Macintosh Business Unit took a copy of the file that
generated the fault but there seems to have not been any progress on this
reported that is apparent to me.

The problem has been reported a number of times over the last 4 years. To
explore the postings do a google search on the following phrases - with the
quotes included.

"Formula for R-Squared in Excel"

"Negative RSQ"

"RSQ function gives incorrect result"

"Latest Office Update has error in RSQ calculation"

Two are my postings and two are from others. That makes four people now.

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


On 3/11/09 9:07 PM, in article C7166844.B5A1%b_jordan@xtra.co.nz, "Bob
Jordan" <b_jordan.TakeThisOut@xtra.co.nz> wrote:

> Once again I was stymied by replying to the Mactopia webpage rather than to
> the newsgroup directly.
>
> I posted this comment on the RSQ problem some time back
>
> The negative RSQ problem sounds awfully similar to an Excel 2004 problem
> that has been reported occasionally
>
> Just to put my pennies worth in on this.
>
> I believe that in my case that the Solver was involved
>
> The test function I use is this =RSQ({0,1},{1,0})
>
> It should give a value of +1 but if the fault occurs it gives -1 which
> happens to be the correl result.
>
> I have also seen rsq come up negative on charts as well.
>
> My gut feeling is that it is a fault in the return from the rsq and correl
> functions. I assume they share code and just do the appropriate squaring if
> needed on exit.
>
> I know that Solver in 2004 would screw it up and one had to restart Excel to
> clear the fault. It could be recreated by getting solver to maximise a cell
> that computed the RSQ of two columns - seems a logical way of fitting
> curves?
>
> But I suspect that the fault is more general.
>
> Unfortunately it is not seen often because most people work with positive R
> values and who can tell the difference between RSQs of 0.996 and 0.993
> anyway
>
>
> Pat McMillan at the Macintosh Business Unit took a copy of the file that
> generated the fault but there seems to have not been any progress on this
> reported that is apparent to me.
>
> The problem has been reported a number of times over the last 4 years. To
> explore the postings do a google search on the following phrases - with the
> quotes included.
>
> "Formula for R-Squared in Excel"
>
> "Negative RSQ"
>
> "RSQ function gives incorrect result"
>
> "Latest Office Update has error in RSQ calculation"
>
> Two are my postings and two are from others. That makes four people now.
>


--

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

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
>