View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
joeu2004 joeu2004 is offline
external usenet poster
 
Posts: 2,059
Default Average the POSITIVE values only

On Nov 7, 7:09 pm, John wrote:
How do I average all the numbers in a column but only the
positive numbers in that column?


Two methods come to mind.

1. =average(if(A1:A200,A1:A20))

That is an array formula; commit with ctrl-shift-Enter.

2. =sumif(A1:A20,"=0") / countif(A1:A20,"=0")

That is not an array formula; commit with Enter, as usual