Array Formula for Nth Column Average .. can any one see my error?

[Kbester]

=SUM(IF(MOD(COLUMN(B2:I2),2)=2)B2:I2)=SUM(IF(MOD(COLUMN(B2:I2),2)=2)B2:I2)

=AVERAGE (IF(MOD(COLUMN('RAW DATA ENTRY'!C4:'RAW DATA ENTRY'!Z4),7)=0 )'RAW DATA ENTRY'!C4:'RAW DATA ENTRY'!Z4)

Can Anyone help me? I am trying to work out average and sum of every 2 or 7 columns. I'm pretty new to array formulas and this complexity and excel. Can anyone see where I am going wrong?

Thanks

Kath

 

[ukmikeb]

=SUM(IF(MOD(COLUMN(B2:I2),2)=2)B2:I2)=SUM(IF(MOD(COLUMN(B2:I2),2)=2)B2:I2)
Kath

Hi Kath

Welcome to the MrExcel forum.

Just to get you started on the SUM.
Fyi MOD(COLUMN(B2:I2),2) can only have two results 0 and 1.

In my example below I have shown you how to sum even columns and odd columns in your range :-

Excel Workbook
	B	C	D	E	F	G	H	I	J
2	1	2	3	4	5	6	7	8	20
3									16
Kbester

Excel 2007

Cell Formulas
Range	Formula
J2	=SUMPRODUCT(--(MOD(COLUMN(B2:I2),2))*(B2:I2))
J3	=SUMPRODUCT(--(MOD(COLUMN(B2:I2),2)=0)*(B2:I2))

hth

 

[Aladin Akyurek]

=SUMPRODUCT(--(MOD(COLUMN(B2:I2)-COLUMN(B2),2)=0),B2:I2)

This sums every second number in B2:I2.

Control+shift+enter, not just enter:

=AVERAGE (IF(MOD(COLUMN('RAW DATA ENTRY'!C4:Z4)-COLUMN('RAW DATA ENTRY'!C4),7)=0,'RAW DATA ENTRY'!C4:Z4)))

This averages numbers of every seventh column.

 

[Aladin Akyurek]

In order to avoid including the empty cells in the average, control+shift+enter, not just enter:

Control+shift+enter:

=AVERAGE(IF(MOD(COLUMN('RAW DATA ENTRY'!C4:Z4)-COLUMN('RAW DATA ENTRY'!C4),7)=0,IF(ISNUMBER('RAW DATA ENTRY'!C4:Z4),'RAW DATA ENTRY'!C4:Z4)))
<strike></strike>