Calculating Average

[Khushalay]

Dear friends

I have 6 fields lets say a,b,c,d,e,f and average of these fields are needed based on the condition that if a and b are filled and rest are empty so average to be calculated based on two fields only. If a,b,c,d,e are filled then average needs to be calculated based on 5 fields.

I put nested If statement and conditions but it is picking up only the last Else condition here the average is calculated based on 6 figures whereas I have only a,b,and c filled. According to my requirement if three boxes are filled then average should be calculated dividing by 3 and not 6. For some reason it is not picking up the above if statements.

Then I tried each box afterUpdate event and put the condition accordingly, Still it doesn't work.

please help me out.

Thanks in advance

 

[Minty]

You actual code would help, but i suspect you may be storing the data badly if you have fields like Value1, Value2, Value3 etc...

 

[Khushalay]

My actual fields are TOTAL2, T3, T5, T7, T9, T11 and Im calculating the average in field CT

 

[Minty]

So what is the SQL of your current query that doesn't work, and you shouldn't store the average as a field it should always be calculated.

 

[Khushalay]

Yes, Im calculating it and the result is showing in CT.

I dont hv SQL. I used Nested If statement in vba:

If Not IsEmpty(TOTAL2) And IsEmpty (T3) And IsEmpty (T5) And IsEmpty (T7) And IsEmpty (T9) And IsEmpty (T11) Then
Me.CT=TOTAL2
ElseIf If Not IsEmpty(TOTAL2) And Not IsEmpty (T3) And IsEmpty (T5) And IsEmpty (T7) And IsEmpty (T9) And IsEmpty (T11) Then
Me.CT=Nz(TOTAL2) + Nz(T3)/ Nz(2)
and it follows till the last Else where all are Not IsEmpty and divided by 6

But my code is dividing by 6 only and ignoring all other If statements. Can you please have a look.

thanks

 

[missinglinq]

Is this a Single, Continuous or Datasheet View Form?

Linq ;0)>

 

[sneuberg]

You could use the Nz function to change the empty fields to 0 then you can just add these field. To find the number of fields with entries you could use the IIF function. You can test the following code in the attached database.

Private Sub Calculate_Click()

Dim Total As Double
Dim n As Double

Total = Nz(TOTAL2, 0) + Nz(T3, 0) + Nz(T5, 0) + Nz(T7, 0) + Nz(T9, 0) + Nz(T11, 0)
n = IIf(IsNull(TOTAL2), 0, 1) + IIf(IsNull(T3), 0, 1) + IIf(IsNull(T5), 0, 1) + IIf(IsNull(T7), 0, 1) + IIf(IsNull(T9), 0, 1) + IIf(IsNull(T11), 0, 1)
If n = 0 Then
    Me.CT = Null
    Exit Sub
End If
Me.CT = Total / n

End Sub

 

[Khushalay]

Thanks alot, it worked

 

[sneuberg]

Glad it worked for you but this problem is a good illustration of how difficult things get when the structure is off. Without seeing your database I can't say for sure that it's not normalized correctly but this horizontal type of math suggests that it might be. If these field were in a separate table this problem could have been solved with a simple aggregate query.

If you tell us more about this database or upload it maybe we could give you some suggestions to improve the structure.