Geomean #NUM! error

[kckay]

I have Excel 2007.

I am using geomean to calculate monthly fecal-e. coli values at a waste water treatment plant. The sample frequency is 1-2 samples weekly. There 8 samples in the month of May. These values range from 0 to 120. May 1 is G135; May 31 is G165. With the exception of the header, the G column is numeric with decimal count 0.

The geomean formula I am using is =GEOMEAN(G135:G165). The format of the geomean field is numeric with 2-pt decimal count.

I get the result #NUM! error.

Thank you for assisting me.

 

[VoG]

You cannot calculate a geometric mean with data that includes 0. Maybe this array entered formula

=GEOMEAN(IF(G135:G165<>0,G135:G165))

confirmed with CTRL + Shift + Enter. However, this result will be positively biassed.

 

[shg]

Wouldn't AVERAGE be a more relevant metric? The zeros are certainly significant, and shouldn't be ignored.

 

[kckay]

Removing the 0 value within the record count allowed the geomean to operate correctly.

The CO state regulations indicate 2 ways of calculating the fecal coliform bacteria concentrations:
1) (a*b*c*d*....) to the (1/n) power
OR
2) antilog([log(a)+log(b)+log(c)+log(d)+...]/n

The zero is an actual count; no sample or test is indicated by an empty cell.

 

[VoG]

The State regulations appear to be flawed since either of those will result in errors or incorrect results.

 

[shg]

Amen.

You could have zeros all year, and one bad day would have the same result as a year of bad days.

 

[kckay]

Agreed - politicians writing reg's!

An executive decision has been handed down that when a zero value is encountered, a 1 shall be utilized in the report and spreadsheet.

I really appreciate the help.

 

[shg]

One is a random number; you could equally choose 42 or 5*10-12. If the unit of measurement was percent, 1 would be an awful number.

Water treatment analysis has been around long enough that I'd bet there is a more thought-out method.

Or perhaps readings of zero never occur in practice, and your measurements are either bad or insufficiently sensitive.

Either way, setting them to a random value is papering over the fecal problem.

 

[kckay]

This subject of fecal coliform counts definitely leaves open lots of room for humor - and I really get a kick from your inputs.

Reg's are reg's. Remember, the government knows best.

Now, along those lines: Crap-in, Crap-out. I would like to put a test or filter on each cell (or column if possible) to change a 0 value to a 1 when the user enters the data.

It is a fix to deal with one set of users, but it will be useful for all of the users throught he state.

Thanks.

 

[VoG]

This can be done but I would caution against this since it could be interpreted as falsifying results and it will distort any metric.

Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
    Application.EnableEvents = False
    If Target.Value = 0 Then
        Target.Value = 1
    End If
    Application.EnableEvents = True
End If
End Sub

But, don't do it!