Average while ignoring zeros and errors

[LoneScrunger]

Hello!

I'm trying to return the average, min and max values of a range that will have errors, be blank or have zeros. I have already found an array formula that works getting rid of the errors but the zeros are now a problem.

{=AVERAGE(IF(NOT(ISERROR(D27:G27)),D27:G27))}

I was thinking that an OR might be needed but for the life of me can't figure out where it belongs. I will also be using this formula with both MIN and MAX in the adjacent cells so it would be great if all I have to do is swap those in for AVERAGE. I'm not keen on how array formulas work, just found an example similar to this on the interwebs, and I'm curious why if the array brackets are removed the value returned is 0 and not an error.

Workbook is proprietary so VB is locked and not an option (not sure if it was but wanted to be clear).

Thanks!

 

[par60056]

try:
=SUM(D27:G27)/COUNTIF(D27:G27,">0")

 

[par60056]

sorry didn't notice you had errors.

=SUMIF(D27:G27,">0")/COUNTIF(D27:G27,">0")

 

[Aladin Akyurek]

Hello!

I'm trying to return the average, min and max values of a range that will have errors, be blank or have zeros. I have already found an array formula that works getting rid of the errors but the zeros are now a problem.

{=AVERAGE(IF(NOT(ISERROR(D27:G27)),D27:G27))}

I was thinking that an OR might be needed but for the life of me can't figure out where it belongs. I will also be using this formula with both MIN and MAX in the adjacent cells so it would be great if all I have to do is swap those in for AVERAGE. I'm not keen on how array formulas work, just found an example similar to this on the interwebs, and I'm curious why if the array brackets are removed the value returned is 0 and not an error.

Workbook is proprietary so VB is locked and not an option (not sure if it was but wanted to be clear).

Thanks!

Control+shift+enter:

=AVERAGE(IF(ISNUMBER(D27:G27),D27:G27))

 

[LoneScrunger]

Thanks very much to both of you! Aladin, your formula worked with the MIN function as well (I noticed that I didn't need it for the MAX, since that wouldn't return a zero, right after I posted the question).

Very helpful and I appreciate your input and time.

 

[Aladin Akyurek]

Thanks very much to both of you! Aladin, your formula worked with the MIN function as well (I noticed that I didn't need it for the MAX, since that wouldn't return a zero, right after I posted the question).

Very helpful and I appreciate your input and time.

You are welcome. If the range of interest itself might contain error values, the MAX version needs also the ISNUMBER test.