basic averaging question 1

[mortac8]

I have a fairly basic question. I have 4 columns of data:

Measurement1 Measurement2 Measurement3 Measurement4
============ ============ ============ ============
7100 7105 7200 7112
7202 7302 7155 7010


How can I calculate the average of all the columns (to get an answer of like 7150)? Also sometimes Measurement3 or Measurement4 columns are empty and I just need the average of Measurement1 and Measurement2. Any ideas?

 

[AdrianRutter]

How about creating an object called "Average of measurements."

(Sum(Measurement1, Measurement2, Measurement3, Measurement4))/4

You would need to write an if statement based around the IsNULL statement if you wanted to do the 4 or 3

Something like (written without verification)

If IsNull(Measurement3) Then (Sum(Measurement1, Measurement2, Measurement3)/3) Else (If IsNull(Measurement4) Then (Sum(Measurement1, Measurement2)/2) Else (Sum(Measurement1, Measurement2, Measurement3, Measurement4)/4))

Hope this helps. (Please rate me ;-))

Adrian Rutter
Consultant - UK