Calculating average of 3. Sometimes 2 out of 3.

[KyleBell]

Hi,

I'm a newbie and am stuck with doing somebody else's work that they did not finish before leaving.

Anyway, I have three columns named [Staff], [Student] and [Peers]. I need to grab the values from those three fields and calculate the average value. Normally, I'd use ([Staff]+[Student]+[Peer]/3), but there are instances where the value of Student has no value, and I want the average out of two values.

An example of this:
[Staff] | [Student] | [Peer] | [Average]
1 | | 3 | 2
2 | 3 | 4 | 3.5

Sorry if I sound totally clueless, I probably am. Can anyone help me with this?

Thanks

 

[mahenkj2]

Try this :

SELECT Table3.ID, Table3.A, Table3.B, Table3.C, IIf(((IIf([A]=0,0,1))+(IIf(=0,0,1))+(IIf([C]=0,0,1)))=0,0,(([A]++[C])/((IIf([A]=0,0,1))+(IIf(=0,0,1))+(IIf([C]=0,0,1))))) AS [AVG]
FROM Table3;

replace table name Table3 with your table name and fields A,B & C from your fields.

 

[pr2-eugin]

Hi Kyle, the above code is how you should go around, but it is not completely correct, (sorry mahenkj2) I did some changes to the code.. just replace AVG as follows..

AVG: (Nz([Staff],0)+Nz([Student],0)+Nz([Peer],0))/((IIf(IsNull([Staff]),0,1))+(IIf(IsNull([Student]),0,1))+(IIf(IsNull([Peer]),0,1)))

In general, your code should be as,

SELECT [COLOR=Blue]SSP.ID[/COLOR], [COLOR=Blue]SSP.[COLOR=Black]Staff[/COLOR][/COLOR], [COLOR=Blue]SSP.[COLOR=Black]Student[/COLOR][/COLOR], [COLOR=Blue]SSP[/COLOR].Peer, (Nz([Staff],0)+Nz([Student],0)+Nz([Peer],0))/((IIf(IsNull([Staff]),0,1))+(IIf(IsNull([Student]),0,1))+(IIf(IsNull([Peer]),0,1))) AS [AVG]
FROM [COLOR=Blue]SSP[/COLOR];

Replace blue bits..

 

[mahenkj2]

Correct Paul, I just forgot to mention use of Nz to get through nulls.

 

[KyleBell]

I placed the code in a new query under SQL view and it works. It calculates the average of 2 or 3 values correctly. However, the calculated average is not updated on the Average field in my table.

How can I do that?

In my query's Design View, my "Average" field is

Average: (Nz([Staff],0)+Nz([Student],0)+Nz([Peers],0))/((IIf(IsNull([Staff]),0,1))+(IIf(IsNull([Student]),0,1))+(IIf(IsNull([Peers]),0,1)))

But the Table is left empty because i get an error "Syntax error (comma) in query expression" when i try to add my table

 

[pr2-eugin]

Kyle, I would say please do not do it.. Calculated fields SHOULD NOT BE SAVED IN TABLES.. Saving data that depends on other fields will always cause trouble at a later stages.. Please look into what Allen Browne talks about Calculated fields.

You can have them display in forms, show in queries.. but never store them. Read up on the link.

 

[direptor]

Hi,

If i want to do something similar to the above but instead of leaving the value empty, i want to write NA so that its understandable when i print the information out in a report. Can that be done?

 

[KyleBell]

Paul, thank you so much for your help. I'm done with the calculation part of my work and the rest is simple.

As a side note, is it possible to do what direptor is asking about? Just something I'm curious about.

 

[pr2-eugin]

Hello Kyle and direptor.. I am not sure what you mean, the above code will give a 0 is there is no value in any of the field.. If you want you can add another IIF something like.. IIF([AVG]=0,"N/A",[AVG]) AS aNewField

 

[direptor]

Hi,

The formulas given above is used if for example [Staff], [Student] or [Peers] is blank. However, if i want to fill in a number for 2 of them and write NA for the 3rd, can the formula be modified to allow that since its no longer a blank field but a text field to get the average of 2 instead of 3 results.

 

[mahenkj2]

Formula modification is possible but how would you enter text in a number field, it wont allow you to do so.

 

[direptor]

That field would be a text field instead of a number field