Calculations in Reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

G'day,

I have a report that contains a number of columns that relate to scores for
a particular assessment that a person may achieve. There are 7 columns in all
and in some cases a person may score a zero.

Essentailly I want to place an average column at the end of it. I have found
this to be more difficult than expected. I have typed the calculation into
the control box in the rudimentary fashion i.e. =(column1+column2 etc)/7. And
ordinarily this will work fine. However each part of the assessment process
(column) takes place at different times and thus when we want the report
where they havent finished all assessments and thus have no entry in some
columns the average calculation will obviously calculate the average
incorrectly as it takes the zero's into account.

I have defaulted the value of all columns to zero, because if I leave them
as null, then an error message occurs in the average calculation column.

So I am not sure whether to default to zero or leave as null in the first
instance, and secondly is there A) a better calculation to enter, such as a
rather complex if then calculation(which i would rather avoid if i could), or
B) is there an automated way to do this.

All assitance greatly appreciated, thanks.

ps I am aware that you can do automated functions in group footers etc but
in this case there are no group footers or the like.

Cheers.
 
That's the penalty for calculating across fields rather than across rows
(normalized).
You can try get the denominator using an expression like:
Abs( (Column1<>0) + (Column2<>0) + (Column3 <>=0)...)
 
Back
Top