Can I have blank fields treated as zero in functions

SuzanneH

Hi,

I am trying to average a range of cells. Two questions:

1. Most important - in some cases the cell is empty. That should be treated as a zero but the AVG function is ignoring the blanks and only taking the average of the populated cells.
2. If there happens to be text in the field (there should not be), then the field can be ignored.

Thanks,

Suzanne

Paul Newcome

You could do a SUM divided by a COUNTIFS to specify exactly what should and should not count towards the average.

=SUM(range to sum)/COUNTIFS(range to sum, NOT(ISTEXT(@cell)))

The COUNTIFS portion will count every cell that is not text meaning it will count rows that have a number or a blank.

Paul Newcome

You could do a SUM divided by a COUNTIFS to specify exactly what should and should not count towards the average.

=SUM(range to sum)/COUNTIFS(range to sum, NOT(ISTEXT(@cell)))

The COUNTIFS portion will count every cell that is not text meaning it will count rows that have a number or a blank.

SuzanneH

This is great, thanks so much Paul.

Paul Newcome

Happy to help! 👍️