Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Use Cells To Define Formula Start/End Rows

1. Member
Join Date
9th November 2006
Posts
29

## Use Cells To Define Formula Start/End Rows

Hello Friends!!

I want to ask if it is possible to make the CORREL function to calculate custom periods of data. For example CORREL(E52:E181,F52:F181) calculates correlation for 129 rows of data (181-52). Do you think that the range that the function takes into account can be instead represented as the number of the current cell minus a cell N rows above (e.g. CORREL(current cell’s address in column E – N rows:current cell’s address in column E, current cell’s address in column F – N rows:current cell’s address in column F)) and the N will be set in a separate cell? For example for N=10 the function will look as follows CORREL(E171:E181,F171:F181) and CORREL(E151:E181,F151:F181) when the N is set to 30.

Dima

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: CORREL Function. Calculate Custom Period

Hi Dima,

Welcome to OzGrid!!

Try,

=CORREL(INDEX(A:A,E2):INDEX(A:A,E3),INDEX(B:B,E2):INDEX(B:B,E3))

where E2 houses Start Row and E3 houses End Row.

HTH
Last edited by Krishnakumar; November 10th, 2006 at 13:42.

3. ## Re: CORREL Function. Calculate Custom Period

use this formula

=CORREL(A1:OFFSET(A1,E2-1,0),B1:OFFSET(B1,E2-1,0))

here the number of rows till you want the range is given in cell E2

Excel Video Tutorials / Excel Dashboards Reports

4. Member
Join Date
9th November 2006
Posts
29

## Re: Use Cells To Define Formula Start/End Rows

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno