COLUMN AVERAGES WITH EMPTY CELLS
I'm trying to calculate our hospital ward census (COVID!!!), however on some days the column contains an empty cell /no value.
I'm using the following formula, but to no avail. Getting an error message. Any help is greatly appreciated!
=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell)))
Stay safe.
Best Answer

the basic AVG formula will ignore empty cells but count 0 cells
=AVG([$Count]:[$Count])
If you would like to complicate things you can use the below:
=AVERAGEIF([$Count]:[$Count], ISNUMBER(@cell))
If you want to use collect (as in your current example):
=AVG(COLLECT([$Count]:[$Count], [$Count]:[$Count], IFERROR(ISNUMBER(@cell), 0)))
All 3 of the above formulas should return the same result.
Answers

Which error message are you getting?
You could try an IFERROR function like so...
=IFERROR(AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell))), 0)

Paul, I know you're the Formula Guru, but that didn't work. I basically have a sheet with 365 rows. There are a few extra rows that separate months that do not contain a value in a cell. No value. Some have a value of "0".
For each column of values, will need to run AVE, Mode, Median, Mean, Min and Max.
Any assistance is appreciated. I think our enterprise customer success manager is off for the holiday. Ideas?

Error message is: UNPARSEABLE

the basic AVG formula will ignore empty cells but count 0 cells
=AVG([$Count]:[$Count])
If you would like to complicate things you can use the below:
=AVERAGEIF([$Count]:[$Count], ISNUMBER(@cell))
If you want to use collect (as in your current example):
=AVG(COLLECT([$Count]:[$Count], [$Count]:[$Count], IFERROR(ISNUMBER(@cell), 0)))
All 3 of the above formulas should return the same result.

The formula you have in your original post should only be throwing that particular error if you have entered a column name that doesn't exist in the sheet, but it looks like you have found a working solution already.
Help Article Resources
Categories
Check out the Formula Handbook template!