Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Increment formula by 7 horizontally (Excel 2003)

    A job downloads data to Excel nightly that I need to graph. It's in a format as shown on the Raw Data worksheet in the attached file. The Graphing Data worksheet needs to have the values from columns E and I on the Raw Data sheet. How can I create a formula on the Graphing Data sheet that will put the data from every 7th row on the Raw Data sheet?

    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Increment formula by 7 horizontally (Excel 2003)

    Try this formula in C4 on the Graphing Data sheet:

    =OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C4)-5,4)

    You can fill it down and right. Similarly, use the following formula in C15:

    =OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C15)-16,8)

    and fill down and right.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Increment formula by 7 horizontally (Excel 2003)

    <hr>How can I create a formula on the Graphing Data sheet that will put the data from every 7th row on the Raw Data sheet?
    <hr>

    Do you not mean pull the data? i don't understand!
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Increment formula by 7 horizontally (Excel 2003)

    Ahh...Hans clarified it!

    You can also add if(iserror(.... to the formula Hans provided to erase the #N/A output of blanks. Try this...

    In cell C4: =IF(ISERROR(OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C4)-5,4)),"",OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C4)-5,4))

    In Cell C15: =IF(ISERROR(OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C15)-16,8)),"",OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C15)-16,8))
    Regards,
    Rudi

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increment formula by 7 horizontally (Excel 2003)

    Terrific, thanks.
    I had the right idea using the OFFSET and MATCH functions, just didn't get the syntax right.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increment formula by 7 horizontally (Excel 2003)

    I really appreciate the response, but I'm afraid I still need some help. The formula works great, but I'll need to utilize the same type of offset/match formula, so I need to understand it. In this formula,
    =OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C4)-5,4)
    I understand except for +Row(C4)-5 Can someone please explain that to me? Thankx

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Increment formula by 7 horizontally (Excel 2003)

    =OFFSET('Raw Data'!$A$1,MATCH(C$3,'Raw Data'!$A:$A,0)+ROW(C4)-5,4)

    The part MATCH(C$3,'Raw Data'!$A:$A,0) looks for the value of C3 (the month) in column A on the Raw Data sheet, and returns the index number of where it was found, i.e. the row number in column A. For the first date, this is 1, for the second date, it is 8, etc.
    From the cell where the date is found, we want to go 4 columns to the right (i.e. to column E), and 0 rows down for C4, 1 row down for C5, 2 rows down for C6 etc.
    We need to add -1 to the index for C4 (remember, the index was 1), 0 for C5, 1 for C6 etc. In each case, we add 5 less than the row number of the cell containing the formula. This is accomplished by using +ROW(C4)-5 (which will become +ROW(C5)-5 in C5, and +ROW(C6)-5 in C6 etc.)

    Note: we could also have used ROW() instead of ROW(C4). If no argument is supplied, ROW() returns the row number of the cell containing the formula.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increment formula by 7 horizontally (Excel 2003)

    I get it now. Thank you so much for the explaination. This is going to make my life alot easier!

Posting Permissions

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