Apnea Board Forum - CPAP | Sleep Apnea
Help with Excel Correlation please - Printable Version

+- Apnea Board Forum - CPAP | Sleep Apnea (https://www.apneaboard.com/forums)
+-- Forum: Public Area (https://www.apneaboard.com/forums/Forum-Public-Area)
+--- Forum: Software Support Forum (https://www.apneaboard.com/forums/Forum-Software-Support-Forum)
+--- Thread: Help with Excel Correlation please (/Thread-Help-with-Excel-Correlation-please)



Help with Excel Correlation please - SevereApnea - 03-27-2021

First off, if this is in the wrong Forum, please advise/move.

I am trying to use Excel to figure out which xPAP settings might be correlating with my side effects.

I am using the =correl(xx4:xx368,yy4:yy368) function (and not the data package) in Excel 2016.
This allows the values to update as new data gets entered.

I am getting quite different results depending on whether I leave a cell in an array empty or enter a value =0, and would appreciate advice on which is correct.

In Detailed***.pdf for example I leave the cells empty when there are no side effects.
LeaveCellsEmpty

In Revised Detailed***.pdf I have entered a Zero in the cells for which there are no side effects.
EnterZeroCells

The results are quite different.

To any Excel experts/statisticians out there: which method is correct please?

Thanks in advance.


RE: Help with Excel Correlation please - Sleeprider - 03-28-2021

I don't know how your field are calculated, but a null (empty) entry is not the same as zero. You can do functions as simple as averaging, and the null entry is ignored, not contributing to the average or number.


RE: Help with Excel Correlation please - Crimson Nape - 03-28-2021

It depends on the question you are asking. 1. What is the average of a particular side effect within its own population? Or 2. Out of all the sampling dates, what is the average of a particular side effect?


RE: Help with Excel Correlation please - SevereApnea - 03-29-2021

That's an idea. I can see if I use the Average function with the Correlation function.

Failing that, I am still not sure whether I should be leaving arrays empty when there were no side effects or entering a zero. More to think about.

Project for the Easter Weekend.


RE: Help with Excel Correlation please - Gideon - 03-29-2021

IMHO empty when you have no data.
No side e_ects is data so 0


RE: Help with Excel Correlation please - Crimson Nape - 03-29-2021

As Gideon said, there should be a 0 (zero) if there wasn't any event to report. Otherwise, Excel will use something like, ISBLANK, and will not include that day in its sampling population.