Trying to figure out an equation

[Guest]

i'm trying to do a sum of ( 6 diff Cells)and then / by number of cells that
contain a number greater than zero. The cells aren't in a row though. I
want to just use the cells c10,e10,g10,i10,k10,m10. Any suggestions

Thanks,

Henry

 

[Guest]

Henry,

Try:
=(C10+E10+G10+I10+K10+M10)/((C10>0)+(E10>0)+(G10>0)+(I10>0)+(K10>0)+(M10>0))

HTH,
Ryan

 

[Ron Rosenfeld]

i'm trying to do a sum of ( 6 diff Cells)and then / by number of cells that
contain a number greater than zero. The cells aren't in a row though. I
want to just use the cells c10,e10,g10,i10,k10,m10. Any suggestions

Thanks,

Henry

If you just have the six cells, you can sum them directly:

=SUM(c10,e10,g10,...)

A more general solution, which SUMs the cells in every other column:

=SUMPRODUCT(C10:M10*(MOD(COLUMN(C10:M10),2)=1))

To count the cells in every other column with a value greater than zero:

=SUMPRODUCT((C10:M10>0)*(MOD(COLUMN(C10:M10),2)=1))

You can divide one by the other to get the result you want.

You could also use the following array formula for the same result:

=AVERAGE(IF(((MOD(COLUMN(C10:M10),2)=1)*C10:M10)>0,C10:M10))

To enter an array formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.


--ron

 

[Biff]

=AVERAGE(IF(((MOD(COLUMN(C10:M10),2)=1)*C10:M10)>0,C10:M10))

I wonder if that'll pass the "Aladin" test. <bg>

Biff

 

[Ron Rosenfeld]

=AVERAGE(IF(((MOD(COLUMN(C10:M10),2)=1)*C10:M10)>0,C10:M10))

I wonder if that'll pass the "Aladin" test. <bg>

Biff

What's the Aladin test?
--ron