Average Value from 4 fields?

[chris.mo]

I am using the following expression to display an average of 4 other expressions thus:

Expr21: ([Expr17]+[Expr18]+[Expr19]+[Expr20])/4

How can I change this to show the average when there are only 2 or 3 values with input.

Any help much apreciated.

Chris.

 

[araskas]

Expr21: ( nz([Expr17])+nz([Expr18]) + nz([Expr19]) + nz([Expr20]) )/4

Search for help on NZ. it can convert null to zero and also can do many more things.

 

[chris.mo]

Thanks, but I still have the 0 from expr19 included in the average when only 3 values are input. I want it to add expr16, 17 and 18 and divide by 3. If 18 and 19 are both 0, it should add 16 and 17 and divide by 2.

I do understand some boolean and logic expressions, the syntax access requres keeps messing me around!!

Thanks again

Chris.

 

[Pat Hartman]

Use the following as the divisor.

IIf((IIf(IsNull(Expr17),0,1) + IIf(IsNull(Expr18),0,1) + IIf(IsNull(Expr19),0,1) + IIf(IsNull(Expr20),0,1)) = 0 , 1, IIf(IsNull(Expr17),0,1) + IIf(IsNull(Expr18),0,1) + IIf(IsNull(Expr19),0,1) + IIf(IsNull(Expr20),0,1))

The IIf(IsNull(x),0,1) expressions return 0 if the expression is null, otherwise they return 1. Therefore if you add the four of them, the result will be the number of non-null items. However, since it is possible that all 4 fields are null, you can't allow the expression to return zero or you'll get a divide by zero error, hence, the first long expression does the calculation and returns 1 if there are no fields with values or the actual count of the fields with values. The alternative is to do the check for zero and if all the fields are null don't do the divide at all.

 

[chris.mo]

I've spent hours trying to get this right today...and STILL no luck.

Expr17,18,19 and 20 are totals of marks given for various attributes of a soccer player as given by 4 talent scouts A,B,C and D. 17,18,19 and 20 will always be filled in, in order of Scout A,B,C and D's input as they are ALL input from paper forms by the clubs chief scout.

An average of the four scouts marks is needed, so I just added the four and divided the total by 4 - no probs unless only A or A and B or just A,B and C's marks are in then the zero values give the wrong average.

I managed to get it to work if [expr20]=0 with this...

IIf([expr20]=0,([expr17]+[expr18]+[expr19])/3,([expr17]+[expr18]+[expr19]+[expr20])/4)

so I'm trying to extend this IIF statement to include for the time when [expr18]=0, and tried to do it with this...

If([expr19]+[expr20]=0,([expr17]+[expr18])/2),[expr17]

but i cant combine the 2 without running into syntax problems.

H E L P!!!!!!!

 

[The_Doc_Man]

Now this is not for the squeamish, but you could always define a special public function in a general module...

Public Function MyAvg4( A1 as String, A2 as String, A3 as String, A4 as String) as Single

Dim sngNum as Single
Dim sngTot as Single

sngNum = 0.0
sngTot = 0.0

if Nz(A1,"") <> "" then
sngNum = sngNum + 1.0
sngTot = sngTot + CSng(A1)
end if
... {repeat for A2, A3, A4}

if sngNum = 0.0 then
MyAvg4 = 0.0
else
MyAvg4 = sngTot / sngNum
end if

End Function

Once you had this defined, you could use it like you could use any other function in a query.

If you've never worked with modules before, then this might not be for you. But if the function is defined in a general module and is public, you can use it almost anywhere in your DB.

 

[Pat Hartman]

Did you not understand my answer? It shows how to replace the divisor (which you currently have hard-coded as 4) with a number representing how many of the 4 columns actually contain data. The result of the calculation I posted will be a number in the range of 1-4. Division by zero is invalid and will cause an error so a 1 is returned instead.

I believe that your method will require 4! (that's 4 factorial = 1*2*3*4 = 24) "If" stataments to evaluate correctly. Someone who knows how to actually calculate the number of combinations please be sure to correct my statement. High school algebra was a long time ago. My method simply requires counting the number of non-null values to come up with an appropriate divisor.

 

[jwindon]

Maybe you could make some use of this code. I needed to alter my divisor based on the value of an option group where N/A should NOT be included in the sum. Please excuse the lousy variables declared. It was a dummy trial, but it worked. The message boxes are there for my benefit to watch what is going on. You can remove them.

Dim ScoreValue As Integer
ScoreValue = Me.ScoreValue

MsgBox ("ScoreValue is" & ScoreValue)

Dim ScoreTotal As Integer
ScoreTotal = ScoreValue

Const NoOfQuestions = 7

Dim X As Integer
Dim Y As Integer
Dim z As Integer
Dim xx As Integer
Dim yy As Integer
Dim zz As Integer
Dim uu As Integer

X = DCount("[Score1]", "tblEvaluationData", "[Score1]=0 and [MaterialID] = Forms!frmScoringData![MaterialID]")
Y = DCount("[Score2]", "tblEvaluationData", "[Score2]=0 and [MaterialID] = Forms!frmScoringData![MaterialID]")
z = DCount("[Score3]", "tblEvaluationData", "[Score3]=0 and [MaterialID] = Forms!frmScoringData![MaterialID]")
xx = DCount("[Score4]", "tblEvaluationData", "[Score4]=0 and [MaterialID] = Forms!frmScoringData![MaterialID]")
yy = DCount("[Score5]", "tblEvaluationData", "[Score5]=0 and [MaterialID] = Forms!frmScoringData![MaterialID]")
zz = DCount("[Score6]", "tblEvaluationData", "[Score6]=0 and [MaterialID] = Forms!frmScoringData![MaterialID]")
uu = DCount("[Score7]", "tblEvaluationData", "[Score7]=0 and [MaterialID] = Forms!frmScoringData![MaterialID]")

MsgBox ("Counts are " & X & " " & Y & " " & z & " " & " " & xx & " " & yy & " " & zz & " " & uu)

Dim tx As Integer
tx = X + Y + z + xx + yy + zz + uu

Dim ScoreDivider As Integer
ScoreDivider = NoOfQuestions - tx

MsgBox ("Score divider is " & ScoreDivider)

Dim OveralScore As Integer
OveralScore = ScoreValue / ScoreDivider

MsgBox "The score for this record is " & OveralScore
Me.ctlFinalScore = OveralScore





[This message has been edited by jwindon (edited 11-21-2001).]

 

[chris.mo]

Pat - yes, I did understand your answer, and thought it would work perfectly. As you can see from the query extract below, when I made Expr20 a zero, I expected a 3 for expr22 (your code pasted in) instead, it remained at 4 so I gave up and started experementing with my own method (to no avail!)

Expr17 Expr18 Expr19 Expr20 Average Mark Expr22
198 194 188 0 193.333333333333 4
204 204 221 218 211.75 4
219 224 227 231 225.25 4
143 143 146 140 143 4

The public function looks good as do the other bits of VB posted, but I'm sorry folks, I don't do VB and dont know how to enter public functions - maybe next year eh? ;-)

Thanks again anyway for all the help guys, if I can get Pats expression or my attempted one to work, I'll be a happy bunny!

Cheers,

Chris.

 

[Rich]

You are getting the answer 4 when putting 0 in Text20 because it is a value and not a null entry. There is a difference between Zero and Null. Pats multiple Iif will have to be changed if you want only want to count values >Zero. I hope you can understand the explanation, post back if not.
[This message has been edited by Rich (edited 11-22-2001).]

[This message has been edited by Rich (edited 11-22-2001).]

 

[simongallop]

Rich is correct. Pat gave you the formula for cells that are Null ie no data has been entered. Reading through it all it looks as if you are saying that all four fields will have a number in them but you want to ignore the average if it is a 0. So if your fields were 8, 0, 6, 4 then you would want (8+6+4)/3 rather than the default of dividing by 4.

Statement would be similar to Pat's, preventing dividing by 0 (or error generated)

I will call the fields A, B, C & D

(A+B+C+D)/iif((A+B+C+D)=0,1,iif(A>0,1,0)+iif(B>0,1,0)+iif(C>0,1,0)+iif(D>0,1,0))

HTH

 

[chris.mo]

Brilliant Harry........it's not Potter is it? Your solution worked like magic! ;-)

Thanks to everyone who gave input too.

Chris.

 

[KKilfoil]

Harry: In this example, where A,B,C,and D are always positive, this is fine.

However, if you need to average values which are allowed to be negative, you should put abs(x) around each occurance of A,B,C,or D in the last four IIF functions of your expression, or the negative values will be ignored.