COLUMN AVERAGES WITH EMPTY CELLS

Lamphere

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.

Leibel S

@Lamphere

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.

Paul Newcome

Which error message are you getting?

You could try an IFERROR function like so...

=IFERROR(AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell))), 0)

Lamphere

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?

Lamphere

Error message is: UNPARSEABLE

Leibel S

@Lamphere

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.

Paul Newcome

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.