IF and Subtract Formula


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.

Best Answer


  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    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
    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!