I’ve been working on the BTS Project Tracker dashboard. I’ve found another capability that I needed to learn before I can complete the dashboard.  I need to find the average of non-zero values.

I’m trying to track incomplete tasks whose duration exceeds the average duration for that task.

The problem arises when calculating the average interval required to complete each task.  I want to exclude any incomplete BTS-task from the calculation of the average interval.

To say that another way, the average interval should be based upon only those sites for which that task is completed already.

Approach

So now that we have defined the problem clearly, how do I go about solving it?  How do I determine which cells in a range a zero value?  Like with most problems in Excel there’s more than one way to do it.  I used the FREQUENCY function which, in general, when given a range and a list of values will tell you how many cells in a range are less than or equal to each value.  You can use Excel help or Google for the frequency function to learn more about how it operates.

Download Demo Workbook to Follow Along

The FREQUENCY function takes 2 arguments: a range and an array of values.  For each value in the array, FREQUENCY will count the number of cells in the range whose value is less than or equal t that array value.  FREQUENCY returns one more than the number of array elements. 

For the range argument, I have calculated the interval between tasks for all completed site-tasks.  Any incomplete site-task displays zero.  These values are in a single column.  And for the array argument, my array has one element: the value zero. 

Using the FREQUENCY Function

This means FREQUENCY will return two values.  The first value is the number of cells in the range whose value is less than or equal to zero.  This will be the count of sites for which this task is not yet complete.  Calculating the average does not use this value.

The second value returned by FREQUENCY is the number of cells whose value is greater than zero.  I want this value because it represents the number of sites for which this task has already been completed.

I should note that the FREQUENCY function is what Excel calls an array formula. Array formulas in Excel are an entirely separate topic which I won’t try to cover here.  For now, although the FREQUENCY function is an array formula, I only want to use the last value returned by FREQUENCY.  Therefore I don’t need the array formula capability.  Frequency returns the value I want even if I don’t enter it as an array formula.

So now let’s look at putting together this formula to calculate the average of all non-zero values.

Procedure

Here is the formula I need to calculate the average of all non-zero values:

SUM(C5:C27)/INDEX(FREQUENCY(C5:C27,0),2)

I’ve already discussed FREQUENCY(C5:C27,0).  It returns two values: the count of all cells in the range C5:C27 which are less than or equal to zero, and a count of all cells in that range whose value is greater than zero.  These 2 values are passed as an array to the INDEX function.

In this case, INDEX takes 2 arguments: an array and a row number.  The array is comprised of the results returned by FREQUENCY.   The row number selects which array element for INDEX to return.  Here is the formula for INDEX:

INDEX(FREQUENCY(C5:C27,0),2)

This returns the count of cells having a non-zero value.  The way the average is calculated is to sum the elements in the range C5:C27, then divides by the number of non-zero cells in that range.  Using SUM is pretty straightforward, so here is the final formula:

SUM(C5:C27)/INDEX(FREQUENCY(C5:C27,0),2)

Update

Reader Steve Schweikert, MBA, pointed out that a far simpler way exists: 

AVERAGEIF(C5:C27,">0")

AVERAGEIF averages all values in the range (the first argument) which meet the criteria expressed in the second argument. This is so much simpler as to be laughable. Many thanks, Steven!

Conclusion

There you have it.  A simple way to calculate the average of all non-zero values in a list.  I expect to use this trick in the BTS Project Tracker dashboard I’m working on.  It adds one more bit of automated project information to an already outstanding Project Tracker.

Download Demo Workbook