Average on a series of fields

[Adam]

Hi All,

I'm working on a form and need to add a text box which calculates the
average of 6 other fields on the form. I have this in the text box:
=([Skills - Achieved]+[Process & Systems - Achieved]+[Knowledge -
Achieved]+[Adding Value - Achieved]+[Case Management - Achieved]+[Spend
- Achieved])/6

Problem is, is that sometimes one of the fields is populated with N/A
or some other text if the figure isn't available. Is there anyway in
this case that the calculation would calculate the remaining fields?

I can get this to work in Excel with the average function .i.e
=Average(A2:A8). If there was text in the range A2:A8 then it would
ignore it and calculate the average on the remaining numbers.


I hope this is possible !

Many Thanks

Adam

 

[Andy Couch - UK Access User Group]

If you surround each of the fields with an immediate if function, then you
can make a substitution for "N/A".

for example

IIf([Skills - Achieved] = "N/A", 0, [Skills - Achieved])

So the formulae becomes
=(IIf([Skills - Achieved] = "N/A", 0, [Skills - Achieved])+[Process &
Systems - Achieved]+[Knowledge -Achieved]+[Adding Value - Achieved]+[Case
Management - Achieved]+[Spend - Achieved])/6

Then repeat for the other fields.

If you have NULL values in the fields this can also cause further problems,
as the average of (1+NULL+1)/3 = NULL. The NZ function can be used to get
around this.

IIf([Skills - Achieved] = "N/A", 0, Nz([Skills - Achieved]))

I just hope you don't have to calculate an average excluding the null
values. for example should (1+Nz(NULL)+1)/3 in fact be (1+1)/2. That is
averaging only over 2 values.

Averages can be quite complex.

 

[Sheila D]

Are you sure this is working correctly? If your fields are numeric you can't
put text in them and if they are text then you can't do numeric calculations

 

[Brendan Reynolds]

The Val() function will help, as it returns 0 if the argument can't be
evaluated as a number, so Val(10) + Val("N/A") = 10. If your text boxes will
always contain some value, then you just need to wrap each reference to a
text box in a Val() function ...

=(Val([Skills - Achieved]) + Val([Process & Systems - Achieved]) etc.

If any of the text boxes might contain the Null value, though, you'll have
to eliminate those Null values first, because Null isn't a valid argument
for the Val() function. So you'll have to wrap the reference to the text box
in a call to the NZ() function, then wrap the call to the NZ() function in a
call to the Val() function, like so ...

=(Val(NZ([Skills - Achieved],0)) + Val(NZ([Process & Systems - Achieved]),
0) etc.

See "Val Function" and "NZ Function" in the help files for details.