2 Replies Latest reply: May 4, 2017 7:37 AM by Hugo Wortel

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

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!

• ###### Re: How to calculate the average of different fields with incidental null values??

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

• ###### Re: How to calculate the average of different fields with incidental null values??

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!