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

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.
Answers

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.

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.

Nic, that fixed it, thanks so much!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 210 Industry Talk
 441 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 301 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!