AVG function dealing with nulls

[mc19qlik]

Hi,

I'm trying to produce an average for 6 weighted values, where any of the 6 values can be null and more than 1 can be null at any one time.

I've read some responses to other questions that state that the AVG function deals with nulls by ignoring them and calculating an average over the remaining values i.e. if you have 1 null, it would calculate the average by summing the remaining 5 and dividing by 5. This is the exact behaviour that I'm looking for, however when I apply the AVG function over 6 variables, whenever 1 or more is null, the result of the AVG function is NULL too.

The expression for the column in the table is:

=AVG((var1*$(vWeight1))+(var2*$(vWeight2))+...+(var6*$(vWeight6)))

(used the ... to save some boredom in reading a repetetive formula)

Any advice would be greatly appreciated in resolving the issue here.

Thanks

[Gysbert_Wassenaar · Accepted solution]

What you're doing is taking an average of the sum of some things. And the + operator does not ignore nulls. If you sum anything with null then the result is null too.
BTW, the result of that sum is at best one single result and the average of that is of course that same result. What you're looking for is probably the RangeAvg function, which will also ignore nulls:
RangeAvg( var1*$(vWeight1), var2*$(vWeight2), ..., var6*$(vWeight6) )

[mc19qlik]

Thanks for the super fast answer! I'm using an average because the columns present and therefore the properties the function is aggregating over will change based on some button presses, so I thought it made sense to use an average. Maybe not though!

The other key benefit from using an average (vs. using the + signs, as I've now found out) is that I can take advantage of the ignoring nulls property. I didn't know that + doesn't ignore nulls, so thank you for. 

The RangeAvg solution works perfectly, thanks very much. I'd heard about that function but wasn't really sure how to apply it, so I really appreciate your example.

Thanks again!