# AVerage Formula Skipping Rows

#### rafamilanes21

##### New Member
Hello guys,

I am new to excel and I'm having a little trouble tying to figure this one out.

I have a huge set of data and I do not want to separate them.

I want to take an average from B2 but skip every other 2 rows. Essentially I want the averages of B2,B5,B8 and so on.

Thanks,

Rafa

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### mole999

##### Moderator
in a non technological way i would use a helper cell..

in the helper B2 = 1, B3 = 2, B4 = 3, then drag down so that the cells fil 1 2 3 on every line, then filter on 1

#### FormR

##### MrExcel MVP
Welcome to the forum;

Perhaps this array formula..

Excel Workbook
BCD
211.5
399
499
52
699
799
81
999
1099
112
1299
1399
141
1599
1699
172
Sheet1

##### MrExcel MVP
Hello guys,

I am new to excel and I'm having a little trouble tying to figure this one out.

I have a huge set of data and I do not want to separate them.

I want to take an average from B2 but skip every other 2 rows. Essentially I want the averages of B2,B5,B8 and so on.

Thanks,

Rafa

Control+shift+enter, not just enter:

=AVERAGE(IF(MOD(ROW(B2:B200)-ROW(B2),3)=0,IF(ISNUMBER(B2:B200),B2:B200)))

#### rafamilanes21

##### New Member
Thanks guys, very helpful. I understood the help cells from mole999. Thanks it worked out just as the others but what is the layout or what is this saying =AVERAGE(IF(MOD(ROW(B2:B200)-ROW(B2),3)=0,IF(ISNUMBER(B2:B200),B2:B200)))

The average understood, the IF function I have an idea. IF is a logical function so it is probably trying to state to skip the rows. But I am kind of lost in trying to understand this formula to relate to it much better.

Thanks,

Newie...

##### MrExcel MVP
Thanks guys, very helpful. I understood the help cells from mole999. Thanks it worked out just as the others but what is the layout or what is this saying =AVERAGE(IF(MOD(ROW(B2:B200)-ROW(B2),3)=0,IF(ISNUMBER(B2:B200),B2:B200)))

The average understood, the IF function I have an idea. IF is a logical function so it is probably trying to state to skip the rows. But I am kind of lost in trying to understand this formula to relate to it much better.

Thanks,

Newie...

The first IF says filters out every thisr cell starting with the initial cell, the second IF picks out only the numeric values that occupy every third cell and hands them to the surrounding AVERAGE. In order to see how the MOD bit evaluates, select the MOD expression on the formula bar, and hit F9. That will show you how the expression proceeds. Hit the escape key and continue with other expressions/terms if needed.

Replies
0
Views
297
Replies
5
Views
73
Replies
1
Views
342
Replies
14
Views
199
Replies
3
Views
56

1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back