I have a column of numbers (zero to 99) that i want the average of the
numbers that are more than zero. Any idea on how to do that the best way???

Try one of these:

Normally entered:

=SUM(A1:A10)/COUNTIF(A1:A10,"0")

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=AVERAGE(IF(A1:A10,A1:A10))

Biff

"mr_concrete" wrote in message
...
I have a column of numbers (zero to 99) that i want the average of the
numbers that are more than zero. Any idea on how to do that the best
way???

=SUM(A1:A100)/(SUMPRODUCT((A1:A100<0)*1))

try this and alter range to suit

"mr_concrete" wrote:

I have a column of numbers (zero to 99) that i want the average of the
numbers that are more than zero. Any idea on how to do that the best way???

=AVERAGE(IF(A1:A1000, A1:A100,""))

This is an array formula. Use CTRL + SHIFT + ENTER to enter it in A101

If done properly, Excel will place { } around the formula.


Gord Dibben MS Excel MVP

On Wed, 7 Feb 2007 11:54:01 -0800, mr_concrete
wrote:

I have a column of numbers (zero to 99) that i want the average of the
numbers that are more than zero. Any idea on how to do that the best way???