Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Use Cells To Define Formula Start/End Rows

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

    Hoping for an answer))

    Dima
    Attached Files

  • #2
    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
    Attached Files
    Last edited by Krishnakumar; November 10th, 2006, 13:42.
    Kris

    ExcelFox

    Comment


    • #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

      Comment


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

        thanks!!! that was really helpful

        Comment

        Trending

        Collapse

        There are no results that meet this criteria.

        Working...
        X