correlation between 2 dates

tmmellor1981

Board Regular
Joined
Jan 18, 2009
Messages
122
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

Board Regular
Joined
Jan 18, 2009
Messages
122
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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

Board Regular
Joined
Jan 18, 2009
Messages
122
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
 
Last edited:

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

Board Regular
Joined
Jan 18, 2009
Messages
122
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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

Board Regular
Joined
Jan 18, 2009
Messages
122
=if(account_opened!b:b>=a1&if(account_opened!b:b <d1),correl(account_opened!p:p,account_opened!av:av)

Hope that makes sense
 

Watch MrExcel Video

Forum statistics

Threads
1,095,369
Messages
5,444,057
Members
405,264
Latest member
JohnP1972

This Week's Hot Topics

Top