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.

**Excel-formulas-average-a-range-and-ignore-zeros-0-value**

*“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:

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:

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!

Steve=True

add a row K to put an X identifying which columns (c,e,g,i,k) to average and then use this formula

=AVERAGEIFS($B5:$K5,$B$6:$K$6,”X”,$B5:$K5,””””,$B5:$K5,”0″)