Friday Challenge – AverageIf NOT Blank or NOT Zero with Negative Values for Non-Contiguous Ranges

In a  recent Friday Challenge, you were tasked with creating a formula that would work out for a non-contiguous (not connected) range that ignored Zero and Ignored Blanks.  But, what we didn’t take into account was how to modify the formula to react to negative values in our average non-contiguous range.

Here was the original challenge and some responses:

Friday Challenge – AverageIf NOT Blank or NOT Zero – NOT So Easy

Here was my solution and other responses:

How-to AverageIf Excluding Zeros and Blanks for Non-Contiguous Ranges in Excel


So the challenge is, how to modify either of these solutions for negative values.

If we enter these values, our previous solution will return 0.5 as the average when it should be 0.333

C3= Blank

E3= 0

G3= 1

I3= -10

K3= 10

Here is the challenge data file for download: Challenge-Data-Average-Excluding-Zeros-and-Blanks-and-Includes-Negative-Values-for-Non-Adjacent-Columns.xlsx

Let me know how you solve adding negative values to this calculation in the comments below.