- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Tags:
- average
- null values
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!