Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!