Formulae: Ignoring Blank Cells When Calculating Averages

Paul · March 7, 2011 4:08AM

When using the AVG function in a formula field, Omniscope interprets a blank cell as a 0 such that it calculates the average (mean) of 10, blank, blank, blank as 2.5

Could someone help me please with the best way to configure Omniscope to calculate an average which ignores or excludes the blank cells, such that it calculates the average of the 4 fields in the above example as 10, as Excel would do as standard using the AVERAGE function?

The above example is the first row in a dataset - subsequent rows have decimal values in all 4 fields and so the AVG function calculates the average 'correctly'

Thanks, Paul

chris · March 7, 2011 4:45AM

Hi Paul,

Thanks for the question.

The AVG function should ignore null values when calculating the average across one or more fields. It doesn't do this at the moment, so this is clearly a bug. We will fix this in tonight's Omniscope 2.6 release.

In the meantime you can use the following formula to calculate the average of all values across 4 fields, ignoring nulls:

([Field 1]+[Field 2]+[Field 3]+[Field 4])
/
(
IF([Field 1]=null, 0,1) + IF([Field 2]=null, 0,1) + IF([Field 3]=null, 0,1) +
IF([Field 4]=null, 0,1)
)

Welcome!

Categories

Ideas Parade

Tagged