Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Use Cells To Define Formula Start/End Rows

  1. #1
    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.

    Hoping for an answer))

    Dima
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Krishnakumar; November 10th, 2006 at 13:42.

  3. #3
    Join Date
    26th July 2004
    Posts
    1,435

    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. #4
    Join Date
    9th November 2006
    Posts
    29

    Re: Use Cells To Define Formula Start/End Rows

    thanks!!! that was really helpful

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Macro to define Name of cells
    By Neil in forum Excel General
    Replies: 2
    Last Post: January 29th, 2012, 09:28
  2. Define Print Range Based On Non Blank Rows
    By crowelde in forum Excel General
    Replies: 2
    Last Post: February 21st, 2008, 11:48
  3. Use Cells For Start & End Formula Reference
    By Avner in forum Excel General
    Replies: 3
    Last Post: January 5th, 2008, 08:49
  4. Deleteing rows within two define ranges
    By jaymat13 in forum Excel General
    Replies: 5
    Last Post: July 12th, 2006, 22:43
  5. Modifiy Define name range formula
    By Fin Fang Foom in forum Excel General
    Replies: 14
    Last Post: March 26th, 2006, 11:28

Bookmarks

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