Average Every Second Column

[Killer]

I was wondering if it's possible to get an average from every second column
listed below?

C8, E8, G8, I8, K8 M8, O8, Q8, R8, T8, V8, X8, Z8, AB8, AD8, AF8, AH8, AJ8

Thanks

 

[Peo Sjoblom]

One way

=AVERAGE(IF(MOD(COLUMN(C8:AJ8),2)=1,IF(C8:AJ8<>"",C8:AJ8)))

entered with ctrl + shift & enter

 

[T. Valko]

If you want every other column starting at C8 then your sequence is out of
order:

C8, E8, G8, I8, K8 M8, O8, Q8, R8, T8, V8, X8, Z8, AB8, AD8, AF8, AH8, AJ8

The order gets messed up after Q8.

Since you don't have a whole lot of cells:

=AVERAGE(C8,E8,G8,I8,K8,M8,O8,Q8,S8,U8,W8,Y8,AA8,AC8,AE8,AG8,AI8)

Or, if there are more cells you can try this array formula** :

=AVERAGE(IF(MOD(COLUMN(C8:AJ8)-COLUMN(C8),2)=0,C8:AJ8))

This formula will not *exclude* empty cells. To exclude empty cells:

=AVERAGE(IF(MOD(COLUMN(C8:AJ8)-COLUMN(C8),2)=0,IF(C8:AJ8<>"",C8:AJ8)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Killer]

Thanks guys for the help I gave this formula a try and the average seems to
be incorrect.

=AVERAGE(IF(MOD(COLUMN(C8:AJ8)-COLUMN(C8),2)=0,C8:AJ8))

Consist of the following
C8 = 7
E8 = 9
G8 = 9

The rest of the columns are blank right now but with the formula above it's
returning a result of 12.4 when it should be only 8.3.

Thanks

 

[T. Valko]

You used the wrong formula. Use this one entered as an array** :

=AVERAGE(IF(MOD(COLUMN(C8:AJ8)-COLUMN(C8),2)=0,IF(C8:AJ8<>"",C8:AJ8)))

Screencap:

http://img66.imageshack.us/img66/9581/avgya5.jpg

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Wondering]

I entered your values 7,9,9 on a new sheet.
I tried both array formulas:
=AVERAGE(IF(MOD(COLUMN(C8:AJ8)-COLUMN(C8),2)=0,C8:AJ8))
and =AVERAGE(IF(MOD(COLUMN(C8:AJ8)-COLUMN(C8),2)=0,IF(C8:AJ8<>"",C8:AJ8)))
The first formula gives the correct answer 1.47 and the second the correct
answer 8.33

I cut and pasted your formula below and got an answer of 8.33 not 12.4 It
appears there may be some other data in row 8 on your sheet.

 

[Killer]

There is data in other cells that's why I asked to count every second column
only but I guess this formula is counting all columns FROM C8:

Want Counted
C8,E8,G8,I8,K8,M8,O8,Q8,S8,U8,W8,Y8,AA8,AC8,AE8,AG8,AI8

Don't wanted counted:
D8,F8,H8,J8,L8,N8,P8,R8,T8,V8,X8,Z8,AB8,AD8,AF8,AH8AJ8

 

[T. Valko]

I guess this formula is counting all columns FROM C8:

The formula *is not* counting all columns.

Screencap:

http://img403.imageshack.us/img403/5785/avg1fx3.jpg

The correct average is 5.

 

[Wondering]

The formulas work if you enter them as array formulas. After typing the
formula, you must press CTRL+SHIFT+ENTER to enter it as an array formula.
Just pressing ENTER will enter the formula, but it won't be an array formula
and will not work properly.

 

[Killer]

I don't understand what's going on here.. I did everything you tired on my
spreadsheet and it's not giving the correct results.

Here's link to the spreadsheet if you don't mind taking a look.
http://nghl.ca/Football_Pool.xls

Thanks

 

[Wondering]

I understand what is going on. The formulas are essentially correct except
that the references to AJ8 should be AI8. The formulas are working properly.
You have the display of 0's suppressed so it looks like you have empty cells
on the spreadsheet when in fact the cells contain 0. The formulas (average,
min, max) are taking these 0's into account. So, for example, the average is
much lower than it should be. You'll have to adjust your formulas
accordingly to exclude 0's. Or make the cells empty when 0 is the result.

Example: Master!I8 is: =SUMIF('W4'!$B$10:$B$49,$B8,'W4'!$AN$10:$AN$49)
This shows as a blank, when in fact the cell contains 0.

It's a good idea not to suppress the display of 0's until you have
everything working properly.

Regards,

Dave

 

[T. Valko]

If you want to exclude 0s from the average and continue to suppress 0
display use this array formula** :

=AVERAGE(IF(MOD(COLUMN(C8:AJ8)-COLUMN(C8),2)=0,IF(C8:AJ8,C8:AJ8)))

I didn't look at the file but I'm assuming all entries in the range are
numeric.

 

[Wondering]

This will work for you when the cells contain 0's: (I changed only the
C8:AI8<> part in the 2nd IF statement)
I changed the formulas in AL8 to
=AVERAGE(IF(MOD(COLUMN(C8:AI8)-COLUMN(C8),2)=0,IF(C8:AI8<>0,C8:AI8)))
AM9 to =MAX(IF(MOD(COLUMN(C8:AI8)-COLUMN(C8),2)=0,IF(C8:AI8<>0,C8:AI8)))
AN9 to =MIN(IF(MOD(COLUMN(C8:AI8)-COLUMN(C8),2)=0,IF(C8:AI8<>0,C8:AI8)))
They are all array formulas. I copied the formulas down to row 47 and Excel
computed the correct answer when the cells contain 0 except for rows 43-47
which give #DIV/0 errors in the average. You could wrap the formula inside
an ISERROR function.

 

[Wondering]

AM9 should be AM8 and AL9 should be AL8 in my last post. But I'm sure you
figured that out.

 

[Killer]

Thanks guys for all your help!

If you want to exclude 0s from the average and continue to suppress 0
display use this array formula** :

=AVERAGE(IF(MOD(COLUMN(C8:AJ8)-COLUMN(C8),2)=0,IF(C8:AJ8,C8:AJ8)))

I didn't look at the file but I'm assuming all entries in the range are
numeric.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks guys for all your help!