correlation between 2 dates

tmmellor1981

Board Regular
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
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
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
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

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
it wont post what i've typed ?

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

Last edited:

XOR LX

Well-known Member

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
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
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
=if(account_opened!b:b>=a1&if(account_opened!b:b <d1),correl(account_opened!p,account_opened!av:av)

Hope that makes sense

Replies
7
Views
118
Replies
14
Views
708
Replies
1
Views
286
Replies
6
Views
289
Replies
4
Views
39

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

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