Correl returning #DIV/0! even with OK columns

**Zarathrustra** · 02-06-2015, 10:59 AM

I am getting the error signal #DIV/0! with some colums even tho the two columns do have data in, are of the same size, and have non-zero StDev - I am using the following formula:

=IF(OR(MD$2735<10,$KQ$2735<10),"",IF(OR(STDEV(MD$1349:MD$2733)=0,STDEV($KQ$1348:$KQ$2732)=0),"",CORREL(MD$1349:MD$2733,$KQ$1348:$KQ$2732)))

I'm using this same formula widely across the spreadsheet and it generally works - except in about 1% of usage.

I had tried including the test for StDev >0 in one OR check, but this gave the same result.

I'm running Excel 2013 in W8.1.

I'd appreciate help.

**Norie** · 02-06-2015, 11:10 AM

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

**Zarathrustra** · 02-06-2015, 12:19 PM

Thanks for responding. Alas, as my file is 7MB+ it exceeds the limit of the Forum. I fear that if I truncate the workbook it will not be able to show the problem.

**joeu2004** · 02-06-2015, 12:51 PM

Originally Posted by Zarathrustra

I am getting the error signal #DIV/0! with some colums even tho the two columns do have data in, are of the same size, and have non-zero StDev - I am using the following formula:

Please Login or Register  to view this content.

I'm using this same formula widely across the spreadsheet and it generally works - except in about 1% of usage.

Try the following work-around:

Please Login or Register  to view this content.

Without seeing actual data that duplicates the problem, I cannot venture a guess about why CORREL might return a #DIV/0 error.

However, I would have changed your original formula as follows:

Please Login or Register  to view this content.

Theoretically, each STDEV might be infinitesimally non-zero, perhaps due to computer binary arithmetic anomalies, but their product is zero. To demonstrate:

1. Enter 1E-300 into A1.
2. Enter =OR(A1=0,A1=0) into A2. It displays FALSE.
3. Enter =A1*A1 into A3. It displays zero.
4. Enter =(A3=0) into A4. It displays TRUE.

**Zarathrustra** · 02-06-2015, 01:50 PM

Many thanks Joe for all that.

Your work-around worked fine, and, for me, that seems more elegant too.

Your final suggestion came up with the same error message as before.

Something definitely odd. It could be a bug, tho' doubtful (but I have had one or two in past years). Probably something odd in my workbook - even when I copied it (without the dependent associated sheets - a 30Mb file) it still had the same problems.

Many thanks

**joeu2004** · 02-06-2015, 02:25 PM

Originally Posted by Zarathrustra

Your final suggestion came up with the same error message as before.

I'm intrigued. Just curious: what if you change STDEV to STDEVP?

(Even though the IFERROR implementation is the better choice.)

If that does not work, I understand why do not want to provide a 30Mb file, but all we should need are the values in MD$1349:MD$2733 and $KQ$1348:$KQ$2732.

Can you copy and paste-value (not just paste) them into a new Excel workbook, and attach the new Excel workbook to a posting here?

First, verify that CORREL duplicates the problem in the new Excel workbook. Sometimes, paste-value alters the binary representation of numbers, and the infinitesimal difference might alter the behavior.

**MrShorty** · 02-06-2015, 02:26 PM

As noted, it is difficult to give specific suggestions without a sample file.

From the help file for the Correl function https://support.office.com/en-us/art...b-239d7b68ca92

Originally Posted by MSExcel help

If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
If either Array1 or Array2 is empty, or if s (the standard deviation) of their values equals zero, CORREL returns the #DIV/0! error value.

You have already stated that the arrays are not blank and that s is not zero. Are you certain the values in the two ranges are actually numbers? These kind of functions tend to ignore "numbers stored as text" (which seems to be a common occurrence). If all of your values are "numbers stored as text", then it will be treated as if the range is empty, and you will get the DIV/0 error.

Beyond that, without a sample file, all I can suggest is to review these reasons for a DIV/0 error and make sure they don't exist.

**Zarathrustra** · 02-06-2015, 03:06 PM

I hadn't thought of that - a mistype. However, I have now checked whether there was any text in my data cells and got the all clear. So that's that ruled out too.

Thanks again for all the help and suggestions.

**David Prendergast** · 11-07-2015, 09:36 AM

Hi all. I hope I can tag on here with similar problem. I have two columns with non-zero and variable numeric contents. I am getting a #DIV/0 error trying to CORREL, even if I just use the following 10 rows:

excel help.jpg

Thanks for any and all help.

David

**Tony Valko** · 11-07-2015, 09:43 AM

The forum owner prefers that you start your own thread.

See forum rule #2:

http://www.excelforum.com/forum-rule...rum-rules.html

Correl returning #DIV/0! even with OK columns

LinkBack

Thread Tools

Rate This Thread

Display

Correl returning #DIV/0! even with OK columns

Re: Correl returning #DIV/0! even with OK columns

Re: Correl returning #DIV/0! even with OK columns

Re: Correl returning #DIV/0! even with OK columns

Re: Correl returning #DIV/0! even with OK columns

Re: Correl returning #DIV/0! even with OK columns

Re: Correl returning #DIV/0! even with OK columns

Re: Correl returning #DIV/0! even with OK columns

Re: Correl returning #DIV/0! even with OK columns

Re: Correl returning #DIV/0! even with OK columns

Thread Information

Users Browsing this Thread

Similar Threads

Educate me on the correl function

Correl using if/then and indirect

correl function

CORREL - IF Command .....

[SOLVED] Does Correl/Rank combo work eg CORREL(cols E & H) where E&H=RANK(.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions