Operator "if" ignores further calls for the same time period while using different variables assigning the same period

Support
1

Hi.

Just sharing an issue with a formula that made me bang my head for hours yesterday.

I was (mistakenly) expecting the “if” operator to be called again even if the specific period in timeStep (being used to set a condition) calls the same period again in another “if” in the same formula (through “if else”).

In another words, if I have this:

var1 = date(y1,m1,d1)
var2 = date(y1,m1,d1) (< same period as var1)

if timeStep=var1 then “X” else if timeStep=var2 then “Y” else 0

(X and Y are arbitrary values, of course)

I was having only “X” printed in the cell for the period “var1” at the variable I wrote in the above formula.

This might be obvious, but in my case I was trying to be smart fixing the dates of billing of two different supplies (two different itens of a “supplies” category), because if any third party delays the delivery of any supply, I would have to just change the date of a particular item in the “supplies” category, and still have a nice variable line showing exactly when it happens. Right!?

Wrong. When the delay actually happened, the new timeStep period was the same as the date of billing of another supply that was also to be accounted in another “if” in the same formula.

This made the subsequent “if” containig the same timeStep period to be ignored (as in the example above) and then I was loosing an expenditure value in the schedule of a section of my financial planning.

This is easy to spot for two variables, but think about dozens of variables in 6 "if"s or so. And that’s why I spent too much time figuring out it and now I’m sharing this like a moral compellment.

Any suggestions to better write a formula that avoid this kind of pitfall?

2

Otto:
It is always hard to follow the details of a complex model when described in a short paragraph by someone who knows it in great detail, so I could be wrong about the following comment.

I’ve always understood the If statement to work in sequence until it found something True and then returned the relevant Then clause, ignoring all the rest of the nested If and Else statements. This is particularly important in cases where subsequent clauses could cause errors. For example if you check a variable to see if it is zero and if it is, return zero, then go on in the Else clause to divide by the variable - you see why you don’t want subsequent clauses to even get consideration. So it seems to me the IF statement is doing what it should and your confusion was simply the result of not understanding the steps the If statement would correctly take.

3

Hi Otto,

For this example I would suggest creating a category called Supplier and attaching a variable called Billing Date to it. Then you can assign a specific billing date to each supplier. You can also create a new variable called Cost which can be assigned per Supplier.

Next step is to create the IF formula where you can type if timeStep=Billing Date then Cost else 0. This will bring in the Cost on the Billing Date for each Supplier as desired.

Please see our docs here for more guidance on Categories.

Hope that helps!

1 Like