I didn’t receive any sample formulas from Friday’s post that included a Friday Challenge, so maybe I stumped everyone when asking you to create an AverageIF Formula Excluding Zeros Across Worksheet Tabs.
Here is the post so that you can learn more about the multi-worksheet tab range reference that was demonstrated.
How-to Easily Sum Across Excel Worksheet Tabs (Plus Friday Challenge)
The Question
In that post I asked if you could create a formula that would calculate the Average excluding zeros (0) across several Excel worksheet tabs using the simple range reference shown in that posting and video.
Maybe everyone thought that AverageIF would work and why bother finding a formula that will work. However… using the simple multi-sheet / multi-tab reference doesn’t work with AverageIF.
The Solution
We need to go back tot he basics like you may have had to use in Excel 2003, before there was an AverageIF statement.
One such solution is to Sum all values across all sheets and divide it by the count of non zero or non-blank values.
We will use a simple sum function for the numerator.
Then we will use a combination of Index and Frequency to count the number of non-blank and non-zero values across all sheets.
The AverageIF Formula Excluding Zeros Across Worksheet Tabs
Assuming your data is in Cell A2 across sheets grouped between “Dec” and “Jan” as you see here:
Here is the entire formula:
=Sum(Dec:Jan!A2)/INDEX(FREQUENCY(Dec:Jan!A2,0),2)
You can use the Formulas>Evaluate Formula action to check out the results step by step:
The numerator of the formula would as follows:
=Sum(Dec:Jan!A2)/
- This is pretty straight forward. If you don’t understand the syntax here, please visit the URL above in the article to learn more.
The denominator of he formula would be as follows:
INDEX(FREQUENCY(Dec:Jan!A2,0),2)
- Let’s break this formula down. Try and start from the inner most parts and move out when evaluating formulas:
- Frequency, this part of the formula will group all the values in cell A2 across the worksheets into those that have a value of zero and those that don’t.
- Index, this will get the 2nd value of the results from the Frequency function.
- And your final result will be the Summation divided by the Index/Frequency Calculation
For a better understanding of how this formula works, please watch the video below.
Video Demonstration
Do you have an alternate solution/formula? Let me know in the comments below if you have a better solution. Thanks!
Steve=True