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
- Customer Resources
- 65.1K Get Help
- 444 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 83 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!