Re: Omit Null Values Calculating Average
The cell formula that I used was long (I'm sure it could have been shorter, but I stopped developing it as soon as I got the right answer) and I couldn't incorporate the
construct since it became too long. The results produced a lot of div by 0 errors where there were no values to average. The macro cleans that up.
Being a long formula it would be easy to introduce mistakes so I set it in stone, so to speak, in the macro.
Disadvantages include having to change the macro if the number of records changes, although it could easily be accommodated with an extra line (maybe 2), but it was way past my bedtime.
If I were to spend more time on it I should be able to shorten the formula (perhaps with sumproduct) to a point where it's more manageable without a macro.