AVerage Formula Skipping Rows

rafamilanes21

New Member
Joined
Feb 9, 2014
Messages
7
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
Joined
Aug 18, 2011
Messages
6,480
Office Version
  1. 365
Platform
  1. Windows
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

New Member
Joined
Feb 9, 2014
Messages
7
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,143
Members
415,880
Latest member
Bruce0203

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top