Results 1 to 5 of 5

Thread: Skipping Rows in Formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2016
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Skipping Rows in Formula

    I'm trying to run a Pearson Correlation [=pearson(array1,array2)] between two columns (e.g. A and B, rows 1-20) but wanted to skip a few rows in both columns (e.g. 11-15). When I try to write the equation, skipping the rows by holding ctrl+highlighting, the equation writes itself as:

    =pearson(A1:A10,A16:A20,B1:B10,B16:B20)

    Which is an invalid function, since there should be only 2 arrays separated by a comma.

    Obviously, I could simply delete rows 11-15, and re-organize the worksheet - but does anyone know how to adjust the formula to correlate A1:A20, B1:B20 - while skipping a few rows (in both columns)?

    Thanks!

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,611
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Skipping Rows in Formula

    =PEARSON(IF((ROW(A1:A20) <= 10) + (ROW(A1:A20) >= 16), A1:A20, FALSE), B1:B20)

    Confirmed with Ctrl+Shift+Enter.

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,057
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Skipping Rows in Formula

    Nice,

    Or if there is some value in an adjescent column to indicate which rows should be included or excluded.
    Like only consider values in A1:B20 IF C1:C20 = "x" <- or whatever

    =PEARSON(IF(C1:C20="x", A1:A20, FALSE), B1:B20)
    Last edited by Jonmo1; Apr 18th, 2016 at 03:27 PM.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    New Member
    Join Date
    Apr 2016
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Skipping Rows in Formula

    Perfect - thank you both!

  5. #5
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,611
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Skipping Rows in Formula

    You're welcome.

    I like Jonmo's suggestion better, because it moves the conditional to the sheet instead of embedded in the formula.

    The ", False" could be removed from both of our formulas.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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