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:
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!