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
Check out the Formula Handbook template!