Calculate average excluding 0 and negative numbers

[sheikhowais]

Hi ,

I have a column of data that includes positive numbers, negative numbers and zeroes. i tried different formulas,
like =SUMIF(G5:G9,">=0")/COUNTIF(G5:G9,">=0")
=AVERAGEIF(G5:G16,"<>0")
but I cant seem to get an average excluding both the zeroes and negative numbers.

I tried your[FONT=&quot]WorkbookExcelMagicTricks686-697[/FONT][FONT=&quot]StartFile&FinishedFile (.xlsx file format) but the formula takes 3 columns of data , when i tried to adjust it to one column it gives me wrong data

Given Formula
=AVERAGE(LARGE((A7:A15,C7:C15,E7:E12,G7:G27),ROW(INDIRECT("1:"&INDEX(FREQUENCY((A7:A15,C7:C15,E7:E12,G7:G27),{0}),2)))))
i want the above formula for only 1 column

[/FONT]Does anyone have an idea how to make this work in Excel ? Thanks!

 

[Repush]

what about:

=SUMPRODUCT(--(B2:B38>0),B2:B38)/SOMPRODUCT(--(B2:B38>0))

 

[tyija1995]

When you put =AVERAGEIF(G5:G16,"<>0") couldn't you just rewrite this as =AVERAGEIF(G5:G16,">0") ?

 

[sheikhowais]

When you put =AVERAGEIF(G5:G16,"<>0") couldn't you just rewrite this as =AVERAGEIF(G5:G16,">0") ?

You are right =AVERAGEIF(G5:G16,">0".. works pretty well..
Thank you