Formula Question - Create logical data

[Karl_Spratt]

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

[AngelosPachis]

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.

[AngelosPachis · Accepted solution]

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]