How to calculate the average of different fields with incidental null values??

[#1]

Hi!

I have a table with 7 fields (columns) which correspond to questions in a survey. It's a bit like this:

1. To what extent do you agree with [statement 1]?

2. To what extent do you agree with [statement 2]?

3. To what extent do you agree with [statement 3]?

...

7. To what extent do you agree with [statement 7]?

The possible values ("the scores") for each field range from 1 to 5, but null values ("N/A") also occur. I would like to calculate the average of these fields per row. That means I have to sum the values and divide them by the number of fields which do not have a null value.

What would be the best solution?

Thanks!

[sunny_talwar · Accepted solution]

You can use RangeAvg() or RangeSum() which ignore text and null values

RangeAvg(Field1, Field2, Field3, Field4,.... Field7) as Avg

or

RangeSum(Field1, Field2, Field3, Field4,.... Field7)/7 as Avg

[#3]

Yes!

You're first solution is what I was looking for. The second one requires a fixed number (7) of fields, but because of the null values some fields don't count.

Thank you, Sunny!