correlation between 2 dates

[tmmellor1981]

Morning All

I'm trying to use the correlation formula but am unable to get it to work between a date range, this is what I currently have:

=CORREL(Account_Opened!$P:$P,Account_Opened!$AV:$AV)

There is a from date in A1 on the current sheet and a too date in D1

I'm sure this can be done but not been able to figure it out of find a post about this

many thanks

 

[XOR LX]

Hi.

Why are you using that particular function? What are you trying to show?

What value does your formula currently return? What should it return, and why?

Why have you mentioned the additional cells A1 and D1 which are not currently part of your formula?

Why are you referencing the entire columns in that formula? Do you genuinely have data going down to row one million-plus? If not, you're forcing an incredible amount of unnecessary calculation.

Perhaps you could post a very small mocked-up dataset of dates, together with what your expected result for that dataset would be, accompanied with a brief explanation.

Regards

 

[tmmellor1981]

I'm comparing survey reponse and looking at the importance of certain aspects of service against NPS (drivers analysis)

It currently returns a figure but not with the required date ranges I require.

A1 & D1 contain dates that I would like building into the formula e.g if it is greater than A1 but less than D1 do the correl formula on the surveys that meet that criteria


There are 40,000 surveys so could limit to 50,000 for now, but is always being added to

hope this clarifies what i 'm trying to do

many thanks

 

[XOR LX]

So the dates in A1 and D1 are on a different sheet?

Also, when you say that the data should be compared to these two values, do you mean that only rows where both the entry in column P and that in column AV satisfy those criteria should be considered?

Regards

 

[tmmellor1981]

Yes there is a raw data sheet with survey extracts and the dates are on a seperate sheet

Yes so for example this format i'm sort of after is

=if(datecolumn,">=A1")&datecolumn,"<D1"),correl(PP:AV)

on only what met that date criteria

sorry if i'm not being perfectly clear

 

[tmmellor1981]

it wont post what i've typed ?

http://www.tiikoni.com/tis/view/?id=9c8ee89

 

[XOR LX]

Yes so for example this format i'm sort of after is

=if(datecolumn,">=A1")&datecolumn,"<d1"),correl(pp:av)

But aren't both column B and column AV date columns? This is why I asked whether that condition needs to be applied to both of these columns, not just one.

Regards</d1"),correl(pp:av)

 

[tmmellor1981]

sorry let me try again
column B contains the date the survey was completed
Column P contains the question i want to correlate
Column AV contains the NPS question
A1 & D1 are on a seperate sheet as selectable date values

 

[XOR LX]

So can you type out what you would like the formula to be?

Ignore the fact that it won't be syntactically valid in Excel. Even use words instead of functions if you want. But just so that it comprises some logical statement with e.g. IF clauses and the correct columns being referenced.

I'll then translate it into the necessary syntax to work in Excel.

Regards

 

[tmmellor1981]

=if(account_opened!b:b>=a1&if(account_opened!b:b <d1),correl(account_opened!p,account_opened!av:av)

Hope that makes sense