Formula Help for Weighted Average Field
Hello,
I am trying to fix a formula for a numeric formula field that calculates the weighted average build cost of house plans we build.
Here is the formula: (([3 Month Average Build Cost] * 10) + ([6 Month Average Build Cost] * 6) + ([1 Year Average Build Cost] * 3) + [2 Year Average Build Cost]) / 20
The Field References are summary fields. The numeric value is the "weight" we are giving to that summary field.
The problem we are having is that occasionally the Summary Field used in the formula is blank/null and it causes the result of the formula to be blank. What I believe needs to happen is for the formula to simply disregard blank values and continue the formula calculation.
For instance, see the attached screenshots. 3 Month Average Build Cost is blank and therefore the "Weighted Average" formula result is blank. Rather, it should still calculate the remainder of the 6mo, 1yr and 2yr averages.
The other obvious issue is that if any part of the formula is blank then the number by which the whole formula is divided would change. In the above formula it is divided by 20 because it assumes there will always be a value in each field. If a field were blank, then the divisor would also need to be reduced by the "weight" of whichever field were blank.
Hopefully I've explained this well and the result I'm looking for is clear. I'm guessing I'm going about the formula all wrong!
------------------------------
Josh Collins
------------------------------
I am trying to fix a formula for a numeric formula field that calculates the weighted average build cost of house plans we build.
Here is the formula: (([3 Month Average Build Cost] * 10) + ([6 Month Average Build Cost] * 6) + ([1 Year Average Build Cost] * 3) + [2 Year Average Build Cost]) / 20
The Field References are summary fields. The numeric value is the "weight" we are giving to that summary field.
The problem we are having is that occasionally the Summary Field used in the formula is blank/null and it causes the result of the formula to be blank. What I believe needs to happen is for the formula to simply disregard blank values and continue the formula calculation.
For instance, see the attached screenshots. 3 Month Average Build Cost is blank and therefore the "Weighted Average" formula result is blank. Rather, it should still calculate the remainder of the 6mo, 1yr and 2yr averages.
The other obvious issue is that if any part of the formula is blank then the number by which the whole formula is divided would change. In the above formula it is divided by 20 because it assumes there will always be a value in each field. If a field were blank, then the divisor would also need to be reduced by the "weight" of whichever field were blank.
Hopefully I've explained this well and the result I'm looking for is clear. I'm guessing I'm going about the formula all wrong!
------------------------------
Josh Collins
------------------------------
