In our last Friday Challenge, I asked you figure out a way to calculate an average for a non-contiguous (non-adjacent) range that will exclude blanks and zeros.

 

This came from a user question on a previous post:

Excel Formulas: Average a Range and Ignore Zero’s (0 Value)

 

where they asked how to do this for non-adjacent columns of data. If you try and use the AverageIf function above, you will get a #Value ErrorIt appears that AverageIf doesn’t work on a non-contiguous range.  So how can we do this?

 

Version 1:

Ken detailed using AverageIfs formula.  Here is how he did it.

1) Add an “X” in a helper row of data so that we can use these in the AverageIfs calculation to determine the non-contiguous data range:

2) Next, we want to create our AverageIfs formula as such:

 

=AVERAGEIFS(B5:K5,B5:K5,”>0″,B6:K6,”X”)

 

Here is a breakdown of the formula:

a) B5:K5 as the average range is used for the values in the average calculation

b) B5:K5,”>0” checks to see if the value in the data is greater than zero and therefore include it in the calculation.  Blanks are automatically excluded as they are not numbers.

c) B6:K6,”X” checks to if there is an X and therefore include that column of data in the average calculation

Note: If you copy and paste this solution, you may have to delete and re-input the “ quotation marks as they may not be the correct quote version for your Excel version.

Note: This solution doesn’t work if you have a negative value in your non-contiguous data range.  Can you figure it out?  If you figure it out, post a comment below with your solution.

 

Version 2:

1) Create the following formula to calculate your own average.  Averages are just a sum of the total amount divided by the count of values.  This formula does just that.

 

=SUM(C3,E3,G3,I3,K3)/INDEX(FREQUENCY((C3,E3,G3,I3,K3),0),2)

 

Here is a breakdown of the formula:

a) =SUM(C3,E3,G3,I3,K3)/ – this is the Numerator and is simply adding the non-contiguous range in a SUM function.  SUM doesn’t have a problem with non-contiguous ranges.

b) INDEX(FREQUENCY((C3,E3,G3,I3,K3),0),2) – this is the Denominator and is a combination of Index and Frequency

b1) FREQUENCY((C3,E3,G3,I3,K3),0) – since frequency can use non-contiguous ranges, we will use this to count the number of times that we have a zero in the data and the number of times that the data is not equal to zero.  The result of this formula will be an array of 2 numbers {Count of Zeros and Blank Values; Count of Greater than Zero Values} – i.e. {1,4}

b2) INDEX({1,4},2) – Since the frequency function we created will return 1 of 2 numbers, we only want to return one of these values as our count for the denominator.  In our case, we have an array of data before the comma and we use the 2 in the value after the comma to show that we want to return the 2nd value (count of greater than zero values).

Note: This solution doesn’t work if you have a negative value in your non-contiguous data range.  Can you figure it out?  This is a good exercise to learn more about Frequency.  If you figure it out, post a comment below with your solution.

 

Video Demonstration

 

File Download

Solutions – Average Excluding Zeros and Blanks for Non-Adjacent Columns

 

What other use cases do you see for Frequency?  The first one that comes to mind is for a histogram graph, but what are other ideas for this function?

 

Steve=True