Hello,
This expression is not discounting the empty columns.
The columns are each ranges from 0 to 400. If there is nothing in the column, it should be zero and be excluded in the calculated average. But Column 1 is 45 and column 2 is 45 but the computed average is 18 (45+45+0+0+0)/5. How do I make it only divide by the number of non-zero columns?
This is my expression:
AVERAGE(
LIST(
IF([Set 1 Weights (lbs)] <> 0, [Set 1 Weights (lbs)],""),
IF([Set 2 Weights (lbs)] <> 0, [Set 2 Weights (lbs)],""),
IF([Set 3 Weights (lbs)] <> 0, [Set 3 Weights (lbs)],""),
IF([Set 4 Weights (lbs)] <> 0, [Set 4 Weights (lbs)],""),
IF([Set 5 Weights (lbs)] <> 0, [Set 5 Weights (lbs)],"")
)
)
Write your own expression instead of using the system provided average function
Simply count the number of non-zero columns by using IF function
If a column has a value greater than zero consider it as one and else zero and total them up
User | Count |
---|---|
43 | |
27 | |
23 | |
16 | |
12 |