Excel 2007 - AVERAGEIF on rows that are non-continuous
 

I have 42 rows of data and I need to average every 3rd row if "1". I've
tried a couple formulas:

1.
averageif(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6, AN6,AQ6,"1",D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,A K6,AN6,AQ6)

I get #VALUE.

2.
=SUM(SUMIF(INDIRECT(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE 6,AH6,AK6,AN6,AQ6),"1"))/SUM(COUNTIF(INDIRECT(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,A E6,AH6,AK6,AN6,AQ6),€�1€�))

The error window shows too few arguments. I found #2 on this website.

Your help would be greatly appreciated.

TC Thanks

Excel 2007 - AVERAGEIF on rows that are non-continuous
 

=AVERAGE(IF((MOD(COLUMN(D6:AQ6),3)=1)*(D6:AQ61),D 6:AQ6))

ctrl+shift+enter, not just enter


"tcbooks" wrote:

I have 42 rows of data and I need to average every 3rd row if "1". I've
tried a couple formulas:

1.
averageif(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6, AN6,AQ6,"1",D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,A K6,AN6,AQ6)

I get #VALUE.

2.
=SUM(SUMIF(INDIRECT(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE 6,AH6,AK6,AN6,AQ6),"1"))/SUM(COUNTIF(INDIRECT(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,A E6,AH6,AK6,AN6,AQ6),€�1€�))

The error window shows too few arguments. I found #2 on this website.

Your help would be greatly appreciated.

TC Thanks

Excel 2007 - AVERAGEIF on rows that are non-continuous
 

You mean columns not rows.

=AVERAGE(IF((MOD(COLUMN(D6:AQ6)-COLUMN(D6),3)=0)*(D6:AQ61),D6:AQ6))

array-entered, Ctl-Shift-Enter, not just Enter

--

HTH

Bob

"tcbooks" wrote in message
...
I have 42 rows of data and I need to average every 3rd row if "1". I've
tried a couple formulas:

1.
averageif(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,AK6, AN6,AQ6,"1",D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE6,AH6,A K6,AN6,AQ6)

I get #VALUE.

2.
=SUM(SUMIF(INDIRECT(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,AE 6,AH6,AK6,AN6,AQ6),"1"))/SUM(COUNTIF(INDIRECT(D6,G6,J6,M6,P6,S6,V6,Y6,AB6,A E6,AH6,AK6,AN6,AQ6),"1"))

The error window shows too few arguments. I found #2 on this website.

Your help would be greatly appreciated.

TC Thanks