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

Yes, it is possible to get an average from every second column listed below in Microsoft Excel. Here are the steps to do it:

1. Select an empty cell where you want to display the average result.
2. Type the following formula in the cell: =AVERAGE(C8:AH8)
3. Press Enter.
4. The above formula will give you the average of all the columns from C8 to AH8. However, you only want to get the average of every second column. To do this, you need to modify the formula.
5. Type the following formula in the cell: =AVERAGE(C8,E8,G8,I8,K8,M8,O8,Q8,R8,T8,V8,X8,Z8,AB 8,AD8,AF8,AH8)
6. Press Enter.
7. The above formula will give you the average of every second column listed below.

That's it! You have successfully calculated the average of every second column in Microsoft Excel.

One way

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

entered with ctrl + shift & enter


--

Regards,

Peo Sjoblom




"Killer" wrote in message
...
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

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,A C8,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)

--
Biff
Microsoft Excel MVP


"Killer" wrote in message
...
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

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" wrote:

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,A C8,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)

--
Biff
Microsoft Excel MVP


"Killer" wrote in message
...
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

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)

--
Biff
Microsoft Excel MVP


"Killer" wrote in message
...
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" wrote:

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,A C8,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)

--
Biff
Microsoft Excel MVP


"Killer" wrote in message
...
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

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" wrote in message
...
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" wrote:

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,A C8,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)

--
Biff
Microsoft Excel MVP


"Killer" wrote in message
...
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

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,AG 8,AI8

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



"Wondering" wrote:

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" wrote in message
...
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" wrote:

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,A C8,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)

--
Biff
Microsoft Excel MVP


"Killer" wrote in message
...
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

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.


--
Biff
Microsoft Excel MVP


"Killer" wrote in message
...
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,AG 8,AI8

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



"Wondering" wrote:

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" wrote in message
...
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" wrote:

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,A C8,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)

--
Biff
Microsoft Excel MVP


"Killer" wrote in message
...
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

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" wrote in message
...
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,AG 8,AI8

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



"Wondering" wrote:

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 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" wrote:

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" wrote in message
...
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,AG 8,AI8

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



"Wondering" wrote:

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

"Killer" wrote in message
...
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" wrote:

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" wrote in message
...
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,AG 8,AI8

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



"Wondering" wrote:

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

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.


--
Biff
Microsoft Excel MVP


"Wondering" wrote in message
t...
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

"Killer" wrote in message
...
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" wrote:

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" wrote in message
...
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,AG 8,AI8

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



"Wondering" wrote:

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

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.

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

Thanks guys for all your help!

"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"Wondering" wrote in message
t...
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

"Killer" wrote in message
...
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" wrote:

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" wrote in message
...
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,AG 8,AI8

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



"Wondering" wrote:

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

You're welcome!

--
Biff
Microsoft Excel MVP


"Killer" wrote in message
...
Thanks guys for all your help!

"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"Wondering" wrote in message
t...
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

"Killer" wrote in message
...
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" wrote:

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" wrote in message
...
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,AG 8,AI8

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



"Wondering" wrote:

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