Mix of conditions on the formula column

Hi,

I’m trying to clean my columns from the exclamation point/“error message”.
I have four columns where I’m calculating the forecast from 2023 to 2026 but if the timeline is empty, I receive the error message.

I’ve teste this formula and it works when it’s by itself: IF({Timeline}=“”,“A”,“B”).
Once I add the other conditions if comes back with exclamation: IF({Timeline}=“”,“A”,IF(AND(YEAR({Timeline#Start})<2023, YEAR({Timeline#End})=2023), WORKDAYS({Timeline#End}, “01/01/2023”)*({Approx \$}/{Duration})),0)

I also have a Duration column so I tried to connect my condition to the Duration column but again, it stops working when I add the other conditions. Formula below:
IF({Duration}=“0”,“A”,IF(AND(YEAR({Timeline#Start})<2023, YEAR({Timeline#End})=2023), WORKDAYS({Timeline#End}, “01/01/2023”)*({Approx \$}/{Duration})),0)

And don’t get me wrong. I’m successfully calculating 4 conditions on the same formula column, the only issue is making the “blank column situation” work with them.

Any help will be welcomed,

Thank you.

Hi Leo,

Difficult to figure out what you’re trying to accomplish but if your original IF statement works as in

``````IF(Cond,A,B)
``````

when you add another IF statement in lieu of B, you should end up with 2 closing parentheses at the end. In your formulas, you don’t.

``````IF(Cond,A,IF(Cond,A,B))
``````

And if you repeat you should end up with 3, etc.

``````IF(Cond,A,IF(Cond,A,IF(Cond,A,B)))
``````

It might be helpful to write it like this to check if the parentheses are right:

``````IF(Cond,A,
IF(Cond,A,B)
)
``````

Hi Gilles,

Thank you. I will try that.

This formula is to split my forecast accordingly to the timeline.
I have variable \$, variable starts and ends, variable durations. I’m using the same formula for for the needed years, just changing the year in the formula to match the column. Don’t bother the column called “Formula”.

Then I would test your formula when there is a duration first. Looks like this would work:

``````IF(AND(YEAR({Timeline#Start})<2023, YEAR({Timeline#End})=2023)), WORKDAYS({Timeline#End}, "01/01/2023")*{Approx \$}/{Duration},0)
``````

Once you got it working,

``````IF({Duration}="0", "A",
paste the formula that works here
)
``````

Unfortunately they don’t seem to work together…

This is the entire formula:
IF(AND(YEAR({Timeline#Start})<2023, YEAR({Timeline#End})=2023), WORKDAYS({Timeline#End}, “01/01/2023”)({Approx \$}/{Duration}), IF(AND(YEAR({Timeline#Start})=2023, YEAR({Timeline#End})=2023), WORKDAYS({Timeline#End},{Timeline#Start})({Approx \$}/{Duration}), IF(AND(YEAR({Timeline#Start})=2023, YEAR({Timeline#End})>2023), WORKDAYS(“12/31/2023”, {Timeline#Start})({Approx \$}/{Duration}), IF(AND(YEAR({Timeline#Start})<2023, YEAR({Timeline#End})>2023), WORKDAYS(“12/31/2023”, “01/01/2023”)({Approx \$}/{Duration}),0))))

If I add the Duration or Timeline condition it doesn’t work.