Forum Discussion

JoshCollins's avatar
JoshCollins
Qrew Assistant Captain
2 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...
MarkShnier__You's avatar
MarkShnier__You
Qrew Champion
2 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
------------------------------