Average of unbound cells?

[C90RanMan]

I am trying to Average values in four unbound cells(named: Dif1, Dif2, Dif3 and Dif4). Some cells will have zero values so I only want to average the cells that have a value greater than zero. Any guidence would be appreciated.

Best Regards

 

[vbaInet]

Your main problem is the divisor with respect to:

1. Getting a count of how non zero values
2. Ensuring that it is never 0 because any number divided by zero gives an error.

So for your first would be to sum the fields up.

Next count how many non zero/non null fields there are using an IF() comparison

Next that the sum is greater than zero which will imply that the denominator too is greater than zero. Again IF() will do.

 

[vbaInet]

I am trying to Average values in four unbound cells(named: Dif1, Dif2, Dif3 and Dif4). Some cells will have zero values so I only want to average the cells that have a value greater than zero. Any guidence would be appreciated.

Best Regards

Double post. Please avoid double posting.

http://www.access-programmers.co.uk/forums/showthread.php?t=196738

 

[C90RanMan]

Yes i did double post as I was uncertain as to which category this should be placed. My appologies.

 

[vbaInet]

I could see you're a newbie

Welcome to the forum.

 

[C90RanMan]

Yes, indeed I am a newbie to creating functions within Access. Would you be so kind and explain the details in what you have previously mentioned? It would be greatly appreciated.

Thanks Again

 

[vbaInet]

I will show you the process:

Sum:

mySum = Val(Nz(Me.Dif1,0)) + Val(Nz(Me.Dif2,0)) + Val(Nz(Me.Dif3,0)) + Val(Nz(Me.Dif4,0))

Count non zero/non null:

If Val(Nz(Me.Dif1,0))<> 0 then
    myVar = 1
end if

If Val(Nz(Me.Dif1,0))<> 0 then
    myVar = 1
end if

If Val(Nz(Me.Dif2,0))<> 0 then
    myVar = myVar + 1
end if

If Val(Nz(Me.Dif3,0))<> 0 then
     myVar = myVar + 1
 end if

If Val(Nz(Me.Dif4,0))<> 0 then
     myVar = myVar + 1
 end if

Final step:

If myVar<> 0 then
     finalValue = mySum/myVar
 end if