IF and Subtract Formula

milbournr

In one column I have an IF formula that is working...

=IF([Project Type]@row = "Quote", "5", IF([Project Type]@row = "Profile", "5", IF([Project Type]@row = "Schedule", "5", IF([Project Type]@row = "Special Project", "3", "False"))))

...While this shows the results (as represented in "Target Duration" below), I cannot get a follow up formula to work on another column for a second formula. The second formula I am using for the Actual vs. Target Duration is below, trying to subtract the Actual from the Target.

The result I am getting is an error: "#INVALID OPERATION", vs. "2"

Any thoughts on why this won't calculate? On a side note I had to change both duration rows from column type duration to text/number, as the IF formula would not show the result as a duration for some reason.

Nic Larsen

I would start by removing the quotes around your numbers. It can just be = 5 or = 3. If your other formula is set up the same, I would adjust it as well.

Nic Larsen

I would start by removing the quotes around your numbers. It can just be = 5 or = 3. If your other formula is set up the same, I would adjust it as well.

Gillian C

Hi @milbournr

Because your formula for the Target Duration is giving the text value "5" rather than the number 5 I believe your Actual vs. Target Duration formula should work if you do the following

=[Actual Duration]@row - VALUE([Target Duration]@row)

*This is assuming that your [Actual Duration] column is two dates subtracted from each other.

If you are still getting an error it may mean that your [Actual Duration] column is also text so try

=Value([Actual Duration]@row) - VALUE([Target Duration]@row)

Hopefully that will work for you.

milbournr

Nic, that fixed it, thanks so much!