Averaging a Column with blank entries

Todd Smelser

How would I go about composing a formula to find the average of a set of number in a column that would ignore blank cells?

Paul Newcome

You are missing the open parenthesis after the COLLECT function, and the range should be entered twice. Once to indicate what range we are going to average and then again to indicate which range we are going to evaluate.

=AVG(COLLECT({Request Tracker Range 9}, {Request Tracker Range 9}, @cell <> 0))

Paul Newcome

The AVG function should be ignoring blank cells already. If it is not, you can try:

=AVG(COLLECT({range to average}, {range to average}, @cell <> ""))

Todd Smelser

When I try it, I get a "DIVIDE BY ZERO" Error. I was assuming that was due to the blank cells but, if the cell has a zero in it, is that a problem?

Paul Newcome

Yes. Zeros will also do that to you. Try this instead.

=AVG(COLLECT({range to average}, {range to average}, @cell <> 0))

Todd Smelser

Thanks. Would that be different for an entire column (as the rows in that column grow daily)?

Todd Smelser

I get UNPARSEABLE on this: =AVG(COLLECT{ Request Tracker Range 9}, @cell <> 0))

Paul Newcome

You are missing the open parenthesis after the COLLECT function, and the range should be entered twice. Once to indicate what range we are going to average and then again to indicate which range we are going to evaluate.

=AVG(COLLECT({Request Tracker Range 9}, {Request Tracker Range 9}, @cell <> 0))

Todd Smelser

That works a treat. Thank you!

Paul Newcome

Happy to help. 👍️