AVerage Formula Skipping Rows

[rafamilanes21]

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.

PLease advise...

Thanks,

Rafa

 

[mole999]

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]

Welcome to the forum;

Perhaps this array formula..

Excel Workbook
	B	C	D
2	1		1.5
3	99
4	99
5	2
6	99
7	99
8	1
9	99
10	99
11	2
12	99
13	99
14	1
15	99
16	99
17	2
Sheet1

 

[Aladin Akyurek]

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.

PLease advise...

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]

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

 

[Aladin Akyurek]

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.