Average If-Then-Else

[Grandma8]

Example:

If P25:P50 is greater than 0, then average P25:P5, else 0

 

[Aladin Akyurek]

Example:

If P25:P50 is greater than 0, then average P25:P5, else 0

One of:

=AVERAGEIF(P25:P50,">0")

=SUMIF(P25:P50,">0")/INDEX(FREQUENCY(P25:P50,0),2)

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(P25:P50),IF(P25:P50>0,P25:P50)))

 

[T. Valko]

Example:

If P25:P50 is greater than 0, then average P25:P5, else 0

Try one of these...

If you're using Excel 2007 or later:

=AVERAGEIF(P25:P50,">0")

This array formula** will work in any version of Excel:

=AVERAGE(IF(P25:P50>0,P25:P50))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Grandma8]

Thank you! I used the last suggestion and it works just fine.

One of:

=AVERAGEIF(P25:P50,">0")

=SUMIF(P25:P50,">0")/INDEX(FREQUENCY(P25:P50,0),2)

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(P25:P50),IF(P25:P50>0,P25:P50)))

 

[Aladin Akyurek]

Thank you! I used the last suggestion and it works just fine.

You are welcome. Thanks for providing feedback.

 

[Abhayk]

I also used your THIRD Formula and it worked perfectly! Thanks!!

Can you please explain why Ctrl + Shift + Enter? Also how to implement it to a series of rows. I have to go to each of the cells & do Ctrl + Shift + Enter. Any solution to this?

One of:

=AVERAGEIF(P25:P50,">0")

=SUMIF(P25:P50,">0")/INDEX(FREQUENCY(P25:P50,0),2)

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(P25:P50),IF(P25:P50>0,P25:P50)))

 

[Aladin Akyurek]

I also used your THIRD Formula and it worked perfectly! Thanks!!

Can you please explain why Ctrl + Shift + Enter? Also how to implement it to a series of rows. I have to go to each of the cells & do Ctrl + Shift + Enter. Any solution to this?

Each of these formulas is a single cell conditional average formula:
https://dl.dropboxusercontent.com/u/65698317/Abhayk%20conditional%20average.xlsx

For control+shift+enter, read on "array formula" in Excel's hep file, reachable via the question mark on the ribbon.