AVERAGEIF over non-contiguous range...there has to be a better way

[ZenZilla]

I'm trying to average three non-contiguous cells that contain referential formulas, based on the criteria that the values are >0 (I want to ignore 0 values in the average). Everything I can think of either doesn't work or will be stupidly complex. There must be a simple way to use criteria formulas (SUMIF, COUNTIF, AVERAGEIF, etc...) with non-contiguous ranges.

Such a simple problem...Thank you for any assistance you can provide.

 

[Jonmo1]

Here's the logic..

Take any numeric value in A1

=1/(1/A1)

That in itself returns the exact same numeric value of A1
If A1 is say 10
1/10 = 0.10
=1/(0.10)

1/0.10 = 10


Now if A1 is a 0
Then 1/0 = #DIV/0! Error

That's where IFERROR comes in.
=IFERROR(1/(1/A1),"")
If the result of 1/(A1/1) is an Error (A1=0), then return "". Otherwise return the result of 1/(1/A1)

It's a very handy trick for hiding Errors 0's and/or Text results of formulas.

 

[XOR LX]

This formula works as a replacement, but I'm not sure I understand the logic. Specifically the use of IFERROR and 1/.

I wrote an article on this technique which may be of interest:

IFERROR techniques for excluding certain values from results « EXCELXOR

The key point being that the only (numerical) value for which reciprocation with 1 will produce an error is 0. Re-reciprocation does not change that error result, though of course it will return any value other than 0 back to its original status after the double-reciprocation.

@XOR LX Thanks for the tip! I will change them to the proper ranges and hopefully I will see an increase in the calculation speed

You're welcome.

Regards

 

[ronjeep68]

Having problems using the averageif function where the range column is formated as TIME and the criteria is a range of times eg. and(>=2:00,<=4:00), and the average range is number of players.
I keep getting the DIV/O error message. How do I solve this problem please?

 

[XOR LX]

Perhaps if you showed us your current formula we might be able to help?

Regards

 

[ronjeep68]

Fixed! Thank you!
Ron