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.

Find more posts tagged with

Accepted answers

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.

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.