COLUMN AVERAGES WITH EMPTY CELLS

Options

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.

Tags:

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!