# Thread: Increment formula by 7 horizontally (Excel 2003)

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

2. ## 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. ## 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!

4. ## 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))

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