RSQ function

[Southward]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

After updating to the latest Office version the other day, I notice that my regression matrices are not working. I type in RSQ but I get the same result as when i type in CORREL or PEARSON - the RSQ function is NOT squaring the correlation coefficient.

 

[Mike Middleton]

Southward -

Please explain "regression matrices ..."

Using Mac Excel 2008 with 12.2.0 update and typing the worksheet functions
for a five-value XY example, I get R from CORREL and PEARSON, and I get R^2
from RSQ.

- Mike

Web http://www.MikeMiddleton.com

 

[Southward]

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

 

[Mike Middleton]

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]

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

 

[Mike Middleton]

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]

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

 

[JE McGimpsey]

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?

 

[JE McGimpsey]

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?

 

[Southward]

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

 

[JE McGimpsey]

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.