Hi Community
Need some help please, I'm trying to create some formulas to identify "Excess" Stock, but in some cases I haven't used any in 12 Months, so I want to say then the stock will = a "Default Value of 60 Months"
I'm trying to create a % Of Demand, My Average Monthly Usage (AMU) and my Months of Stock (Avg_Mths Stock)
My formulas are below.. and I've attached the Sample excel data, if someone can please advise what I'm doing wrong please. I've also noticed the Avg_Mths Stock) column is coming as a NULL value, not as a numeric value, even though the columns are all numeric (Double)
Formula for % Of Dmd
[Available_Excess_Qty]/[SO & WO 12 Mths Dmd]*100
Formula for AMU Qty
[SO & WO 12 Mths Dmd]/12
Formula for Avg_Mths_Stock
IF ([SO & WO 12 Mths Dmd]=0 + [Available_Excess_Qty]>0)THEN 60 ELSEIF
[Available_Excess_Qty]=0 THEN 0 ELSEIF
[Available_Excess_Qty]/[AMU Qty]<0 THEN 0 ELSEIF
[Available_Excess_Qty]/[AMU Qty]=0 THEN 60 ELSE [Available_Excess_Qty]/[AMU Qty] ENDIF
Solved! Go to Solution.
Hi @Karl_Spratt ,
From a quick look your [SO & WO 12 Mths Dmd] field is equal to zero for some records, so causes Nulls to come up, because you are using it as the denominator in your division.
And that explains why
1) you get Null at the "% of Dmd" field (dividing with 0),
2) you get 0 in the AMU Qty (because [SO & WO 12 Mths Dmd] is the numerator and is equal to zero) and
3) you get a Null for Avg Mth Stock, because AMU Qty is in the denominator, but this variable can be 0 because of [SO & WO 12 Mths Dmd]