AVERAGE with conditions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=SUM(A1:A100)/(SUMPRODUCT((A1:A100<>0)*1))

try this and alter range to suit
 
=AVERAGE(IF(A1:A100>0, 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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AVERAGE 7
AVERAGE 2
Average formula works but.... 3
Averages with conditions.... 1
Average formula only works partially..... 6
Average with Conditions 1
Averaging numbers in a column while excluding blank spaces and zer 4
Averaging to-date lists??? 1

Back
Top