AVERAGE
 

How do I average a column of numbers to exclude zeros? If there are 3 zeros
and 2 numbers, then the average function is dividing the two numbers by 5. I
need an average function that will average only numbers not zeros. So, if
there are 5 rows and in the column there a 1 and a 4 and three 0's, then the
average I want is 2.6 because it would only average the numbers above zero.
Thank you. Connie

AVERAGE
 

= AVERAGE(IF(A1:A5<0,A1:A5))
Commit with CTRL SHIFT ENTER

--
HTH,
Barb Reinhardt



"Connie Martin" wrote:

How do I average a column of numbers to exclude zeros? If there are 3 zeros
and 2 numbers, then the average function is dividing the two numbers by 5. I
need an average function that will average only numbers not zeros. So, if
there are 5 rows and in the column there a 1 and a 4 and three 0's, then the
average I want is 2.6 because it would only average the numbers above zero.
Thank you. Connie

AVERAGE
 

Try one of these:

Excel 2007 only:

=AVERAGEIF(A1:A5,"<0")

All versions of Excel array entered** :

=AVERAGE(IF(A1:A5<0,A1:A5))

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


--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
How do I average a column of numbers to exclude zeros? If there are 3
zeros
and 2 numbers, then the average function is dividing the two numbers by 5.
I
need an average function that will average only numbers not zeros. So, if
there are 5 rows and in the column there a 1 and a 4 and three 0's, then
the
average I want is 2.6 because it would only average the numbers above
zero.
Thank you. Connie

AVERAGE
 

Sorry, the array formula is giving me #DIV/0! and I pressed CTRL, SHIFT &
ENTER. What the problem be? Is there an add-in I need? Connie

"T. Valko" wrote:

Try one of these:

Excel 2007 only:

=AVERAGEIF(A1:A5,"<0")

All versions of Excel array entered** :

=AVERAGE(IF(A1:A5<0,A1:A5))

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


--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
How do I average a column of numbers to exclude zeros? If there are 3
zeros
and 2 numbers, then the average function is dividing the two numbers by 5.
I
need an average function that will average only numbers not zeros. So, if
there are 5 rows and in the column there a 1 and a 4 and three 0's, then
the
average I want is 2.6 because it would only average the numbers above
zero.
Thank you. Connie

AVERAGE
 

Sorry, I'm getting #DIV/0! and I pressed CTRL SHIFT ENTER after entering it.
Is there an add-in I need or something? Why doesn't it work? Connie

"Barb Reinhardt" wrote:

= AVERAGE(IF(A1:A5<0,A1:A5))
Commit with CTRL SHIFT ENTER

--
HTH,
Barb Reinhardt



"Connie Martin" wrote:

How do I average a column of numbers to exclude zeros? If there are 3 zeros
and 2 numbers, then the average function is dividing the two numbers by 5. I
need an average function that will average only numbers not zeros. So, if
there are 5 rows and in the column there a 1 and a 4 and three 0's, then the
average I want is 2.6 because it would only average the numbers above zero.
Thank you. Connie

AVERAGE
 

Sorry, my mistake!! This DOES work! Thank you very much. Connie

"Barb Reinhardt" wrote:

= AVERAGE(IF(A1:A5<0,A1:A5))
Commit with CTRL SHIFT ENTER

--
HTH,
Barb Reinhardt



"Connie Martin" wrote:

How do I average a column of numbers to exclude zeros? If there are 3 zeros
and 2 numbers, then the average function is dividing the two numbers by 5. I
need an average function that will average only numbers not zeros. So, if
there are 5 rows and in the column there a 1 and a 4 and three 0's, then the
average I want is 2.6 because it would only average the numbers above zero.
Thank you. Connie

AVERAGE
 

Sorry, my mistake!! This DOES work! Thank you! Connie

"T. Valko" wrote:

Try one of these:

Excel 2007 only:

=AVERAGEIF(A1:A5,"<0")

All versions of Excel array entered** :

=AVERAGE(IF(A1:A5<0,A1:A5))

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


--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
How do I average a column of numbers to exclude zeros? If there are 3
zeros
and 2 numbers, then the average function is dividing the two numbers by 5.
I
need an average function that will average only numbers not zeros. So, if
there are 5 rows and in the column there a 1 and a 4 and three 0's, then
the
average I want is 2.6 because it would only average the numbers above
zero.
Thank you. Connie

AVERAGE
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
Sorry, my mistake!! This DOES work! Thank you! Connie

"T. Valko" wrote:

Try one of these:

Excel 2007 only:

=AVERAGEIF(A1:A5,"<0")

All versions of Excel array entered** :

=AVERAGE(IF(A1:A5<0,A1:A5))

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


--
Biff
Microsoft Excel MVP


"Connie Martin" wrote in message
...
How do I average a column of numbers to exclude zeros? If there are 3
zeros
and 2 numbers, then the average function is dividing the two numbers by
5.
I
need an average function that will average only numbers not zeros. So,
if
there are 5 rows and in the column there a 1 and a 4 and three 0's,
then
the
average I want is 2.6 because it would only average the numbers above
zero.
Thank you. Connie