Division by zero

[Guest]

I have been able to use the following code to display results on a report. I
am trying to use this same code in a query but I keep coming up with errors
including "Division by zero". Some of the fields will be null and cannot have
a "0" for the value. The result of this calculation is an average.

=(Nz([Hour1],0)+Nz([Hour2],0)+Nz([Hour3],0)+Nz([Hour4],0)+Nz([Hour5],0)+Nz([Hour6],0)+Nz([Hour7],0)+Nz([Hour8],0)+Nz([Hour9],0)+Nz([Hour10],0)+Nz([Hour11],0)+Nz([Hour12],0)+[Factored_DT])/(IIf([Hour1]
Is Null,0,1)+IIf([Hour2] Is Null,0,1)+IIf([Hour3] Is Null,0,1)+IIf([Hour4] Is
Null,0,1)+IIf([Hour5] Is Null,0,1)+IIf([Hour6] Is Null,0,1)+IIf([Hour7] Is
Null,0,1)+IIf([Hour8] Is Null,0,1)+IIf([Hour9] Is Null,0,1)+IIf([Hour10] Is
Null,0,1)+IIf([Hour11] Is Null,0,1)+IIf([Hour12] Is Null,0,1))

I also realize this is repetative data and should be in a relating table
because each Hour is it's own field (1-12). Any help or advice on any of the
above would be greatly appreciated.

 

[Duane Hookom]

Normalize, normalize, normalize.
http://www.datamodel.org/NormalizationRules.html

 

[Ken Snell \(MVP\)]

First, yes, you're right.... your table structure is not normalized, and
you're seeing just one of many problems of dealing with unnormalized data
structure -- namely, the ridiculously complex expression that you need to
use to get an average.

What you need is a child table with four fields in it:
tblHourTable
HourID (primary key -- autonumber is fine)
LinkingField (foreign key back to main table)
HourNum (long integer to hold values 1 through 12)
HourValue (field to hold the value for each hour)

The linking field should hold the value of the primary key field for the
record to which the data are related in the main table.

Then your expression becomes:

=Sum(Nz([HourValue],0))+[Factored_DT])/Sum(Abs(Not [HourValue] Is Null))

To correct for the possibility of the entire denominator being a value of
zero:

=IIf(Sum(Abs(Not [HourValue] Is
Null))<>0,Sum(Nz([HourValue],0))+[Factored_DT])/Sum(Abs(Not [HourValue] Is
Null)),"what value you want when denominator is zero")

 

[John Spencer]

As has been pointed out, you really need to normalize this data. One
workaround is to write a custom VBA function and use it in your query.

You could also try something like the following untested expression.
Although the length of the expression may get close to the allowable length
for an expression in the query grid.

IIF(Hour1 is Null and Hour2 is Null and ... and Hour12 is Null, Null,
Nz([Hour1],0)+Nz([Hour2],0)+...+Nz([Hour11],0)+Nz([Hour12],0)+[Factored_DT])/ Abs(Hour1 is Not Null + Hour2 is Not Null + ... + Hour12 is Not Null) )'Sample functionPublic Function fGetMeanAverage(ParamArray Values())'John Spencer UMBC CHPDM'Last Update: April 5, 2000'Calculates the arithmetic average (mean) of a group of values passed to it.'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0) returns 3(21/7)'Ignores values that cannot be treated as numbers.Dim i As Integer, intElementCount As Integer, dblSum As Double intElementCount = 0 dblSum = 0 For i = LBound(Values) To UBound(Values) If IsNumeric(Values(i)) Then 'Ignore Non-numeric values dblSum = dblSum + Values(i) intElementCount = intElementCount + 1 End If Next i If intElementCount > 0 Then fGetMeanAverage = dblSum / intElementCount 'At least one number in thegroup of values Else fGetMeanAverage = Null 'No number in the group of values End IfEnd Function"Cory" <[email protected]> wrote in messagenews:[email protected]...>I have been able to use the following code to display results on a report.I> am trying to use this same code in a query but I keep coming up witherrors> including "Division by zero". Some of the fields will be null and cannothave> a "0" for the value. The result of this calculation is an average.>>=(Nz([Hour1],0)+Nz([Hour2],0)+Nz([Hour3],0)+Nz([Hour4],0)+Nz([Hour5],0)+Nz([Hour6],0)+Nz([Hour7],0)+Nz([Hour8],0)+Nz([Hour9],0)+Nz([Hour10],0)+Nz([Hour11],0)+Nz([Hour12],0)+[Factored_DT])/(IIf([Hour1]> Is Null,0,1)+IIf([Hour2] Is Null,0,1)+IIf([Hour3] Is Null,0,1)+IIf([Hour4]Is> Null,0,1)+IIf([Hour5] Is Null,0,1)+IIf([Hour6] Is Null,0,1)+IIf([Hour7] Is> Null,0,1)+IIf([Hour8] Is Null,0,1)+IIf([Hour9] Is Null,0,1)+IIf([Hour10]Is> Null,0,1)+IIf([Hour11] Is Null,0,1)+IIf([Hour12] Is Null,0,1))>> I also realize this is repetative data and should be in a relating table> because each Hour is it's own field (1-12). Any help or advice on any ofthe> above would be greatly appreciated.

 

[John Spencer]

Sorry about that. I'm no quite sure what happened. So let's try this again.

You could also try something like the following untested expression.
Although the length of the expression may get close to the allowable length
for an expression in the query grid.

IIF(Hour1 is Null and Hour2 is Null and ... and Hour12 is Null, Null,
Nz([Hour1],0)+Nz([Hour2],0)+...+Nz([Hour11],0)+Nz([Hour12],0)+[Factored_DT])/Abs(Hour1 is Not Null + Hour2 is Not Null + ... + Hour12 is Not Null) )'Sample function - paste into a VBA module and savePublic Function fGetMeanAverage(ParamArray Values())'John Spencer UMBC CHPDM'Last Update: April 5, 2000'Calculates the arithmetic average (mean) of a group of values passed to it.'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0) returns3(21/7)'Ignores values that cannot be treated as numbers.Dim i As Integer, intElementCount As Integer, dblSum As Double intElementCount = 0 dblSum = 0 For i = LBound(Values) To UBound(Values) If IsNumeric(Values(i)) Then 'Ignore Non-numeric values dblSum = dblSum + Values(i) intElementCount = intElementCount + 1 End If Next i If intElementCount > 0 Then fGetMeanAverage = dblSum / intElementCount 'At least one number inthegroup of values Else fGetMeanAverage = Null 'No number in the group of values End IfEnd Function"John Spencer" <[email protected]> wrote in messagenews:[email protected]...