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

As an Excel dashboard engineer you will often need to create complex calculations for your charts, tables and displays. So today’s challenge is one that was sent in by a reader from another of my posts.


“Hi really need your help, I would like to know how I can average different cells in different columns and ignoring zeros. For e.g take columns a to k, a will have an average that i need then c will have an average that i need and then e etc. The other columns will have other figures which I do not need. Please help i have used the following but did not help, =AverageIF(c3,e3,g3,i3,k3,”>0″) I have tried messing about with this but not sure where I am going wrong your help will be appreciated, many thanks in advance”  – Shane

It seems like an easy solution, but the trick is that we need to calculate this averageif on non-adjacent columns of data. So I have set up some data for us for this challenge and it looks like this: Data for AverageIf Exclude Zeros and Blanks Challenge

Can you figure it out? Leave a comment with your solution for cell N2 and it should work for all the rows with zeros, blanks and both zeros and blanks: SpreadSheet for AverageIf Exclude Zeros and Blanks Challenge


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


Good luck and thanks for playing the home game!