Average unbound values?

[C90RanMan]

I am attempting to Average across four unbound cells. Some may have values or may not. Dif1, Dif2, Dif3 and Dif4 are the names of the unbound cells. I am using the following formula:



=(Nz([Dif1],0)+Nz([Dif2],0)+Nz([Dif3],0)+Nz([Dif4],0))/(IIf(IsNull([Dif1]),0,1)
+IIf(IsNull([Dif2]),0,1)+IIf(IsNull([Dif3]),0,1)+IIf(IsNull([Dif4]),0,1))



Where: Dif1 = 1
Dif2 = 1
Dif3 = 1
Dif4 = 0

The answer being generated is 6 when it should be 1.

Thanks Again

[TheShabz]

break up the equation into its 2 parts and do those separately and see which one isnt adding correctly.

[TheShabz]

or do avg(Nz([Dif1],0)+Nz([Dif2],0)+Nz([Dif3],0)+Nz([Dif4],0))

[C90RanMan]

Will avg(Nz([Dif1],0)+Nz([Dif2],0)+Nz([Dif3],0)+Nz([Dif4],0)) return the average of just three of the cells, or possibly, even average just two of the cells if for instance the other cells contain zero values?

Thanks!

[TheShabz]

my mistake, misuse of the syntax. i think you'd replace the + with ,
I'm fairly confident that avg() will take an array.

[C90RanMan]

And this will Average only cells that have a value greater than "0" correct?

[TheShabz]

yes and no. there's nothing there indicating the cells must have values of >0. however, what that *should* do is take the average of (in our case) avg(1,1,1,0) and hopefully figuring out not to count the last 0 as part of the total. I've actually never used the avg() function in this manner. only one way to find out though right? let me know what happens.

[C90RanMan]

avg(Nz([Dif1],0),Nz([Dif2],0),Nz([Dif3],0),Nz([Dif4],0)) returns "The expression you entered has a funtion containing the wrong number of arguments"

Thanks

[TheShabz]

guess i was wrong. go back and try what i mentioned in my first reply about breaking up your ((stuff)/(morestuff)) in checking "stuff" and "morestuff" separately to make sure they each return what you want. then you can start working on the smaller chunk.

[C90RanMan]

LOL . . . . I did that earlier and the first part works just fine, so, that tells me the second part after the "/" has an error

[Rawb]

You could always do it in VB. In the On Enter event, put the following:

  Dim nbrFieldsUsed As Integer
  Dim nbrTotal as Double

  nbrFieldsUsed = 0
  nbrTotal = 0

  If Not IsNull(Me!Dif1) Then
    nbrFieldsUsed = nbrFieldsUsed + 1
    nbrTotal = nbrTotal + Me!Dif1
  End If

  If Not IsNull(Me!Dif2) Then
    nbrFieldsUsed = nbrFieldsUsed + 1
    nbrTotal = nbrTotal + Me!Dif2
  End If

  If Not IsNull(Me!Dif3) Then
    nbrFieldsUsed = nbrFieldsUsed + 1
    nbrTotal = nbrTotal + Me!Dif3
  End If

  If Not IsNull(Me!Dif4) Then
    nbrFieldsUsed = nbrFieldsUsed + 1
    nbrTotal = nbrTotal + Me!Dif4
  End If

  If nbrFieldsUsed = 0 Then
    Me!MyAverageField = 0
  Else
    Me!MyAverageField = nbrTotal / nbrFieldsUsed
  End If

Just replace MyAverageField with the name of the Text Box that's supposed to hold your average.

Heck, if you want, you can even have the On Update event of each of the Dif fields point to this function too (so you get real-time average calculations!).

[TheShabz]

way to one-up me there Rawb! lol.

*high five* thanks for the help. i had just about gotten to the end of me sounding like i knew what i was talking about =P

[maximus]

well the original problem seemed to me is trying to deal with null values:
Like use of Nz and isNull in the average expression which C90RunMan had posted.

Well I would rather tackle the problem from the very base like:
the text boxes Dif1, Dif2, Dif3, Dif4

Example Assuming I have a text box that will show the sum of Marks:
=IIf(IsNull(DSum("[Marks]","tblmarks","[ID]=1")),0,DSum("[Marks]","tblmarks","[ID]=1"))

here this expression will insure that either the sum or 0 is returned if the sum value is null. I think then the average calculation can be done quite easily.

[C90RanMan]

Maximus,
I already have the text boxes returning zeros values if the calculation is null. Do you have a recommendation to obtain the Average that is different from Rawbs' suggestion?

[C90RanMan]

Rawb,
With the VB:

Dim nbrFieldsUsed As Integer
Dim nbrTotal as Double

nbrFieldsUsed = 0
nbrTotal = 0

If Not IsNull(Me!Dif1) Then
nbrFieldsUsed = nbrFieldsUsed + 1
nbrTotal = nbrTotal + Me!Dif1
End If

If Not IsNull(Me!Dif2) Then
nbrFieldsUsed = nbrFieldsUsed + 1
nbrTotal = nbrTotal + Me!Dif2
End If

If Not IsNull(Me!Dif3) Then
nbrFieldsUsed = nbrFieldsUsed + 1
nbrTotal = nbrTotal + Me!Dif3
End If

If Not IsNull(Me!Dif4) Then
nbrFieldsUsed = nbrFieldsUsed + 1
nbrTotal = nbrTotal + Me!Dif4
End If

If nbrFieldsUsed = 0 Then
Me!Average1 = 0
Else
Me!Average1 = nbrTotal / nbrFieldsUsed
End If

I get a "Compile Error: User-defined type not defined". Any Suggestions?