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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

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

ADVERTISEMENT

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:

tmmellor1981

Board Regular
Joined
Jan 18, 2009
Messages
122
it wont post what i've typed ?

view


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

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517

ADVERTISEMENT

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,129,561
Messages
5,637,065
Members
416,956
Latest member
mitzhaki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top