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

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

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.