Forum Discussion

JoshCollins's avatar
JoshCollins
Qrew Assistant Captain
3 years ago

Help With Formula 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
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    I will answer twice but best not to post twice  :)

    Try this

    var number NumberOfMonths =
    IF(not IsNull([3 Month Average Build Cost]),10,0)
    + 
    IF(not IsNull([6 Month Average Build Cost]),6,0)
    +
    IF(not IsNull([1 Year Average Build Cost]),3,0)
    +
    IF(not IsNull([2 Year Average Build Cost]),1,0);


    (  nz([3 Month Average Build Cost]) * 10
    + nz([6 Month Average Build Cost]) * 6
    + nz([1 Year Average Build Cost]) * 3)
    + nz([2 Year Average Build Cost]))  / $NumberOfMonths


    // the nz function will turn a null into a zero 

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------