Combining IF Statement and IFERROR

I need some help. I have the following 2 statements that work well alone:

=IFERROR([# Days Since Last Offer]@row, "Auction")

=IF([# Days Since Last Offer]@row >= 180, "Auction")

But I need them in the same formula. I want to return the work Auction IF the # days is 180 and IF an error is being generated in the cell.

Find more posts tagged with

Formulas

Cell linking

Accepted answers

Jeff Reisman

@french2s

=IFERROR([Today's Date]@row - [Date Last Offer Received]@row, 9999) will absolutely work. You either get the result of the subtraction, or if it's an error you get 9999.

See below. I created an index/match to pull a date value into this one [Date End] cell that I knew would get no match. The result then is 9999. The rows where there is an End Date show the result of the subtraction.

You could always use something other than 9999, such as "No Offer Received". Then you'd change you second formula to:

=IF(OR([# Days Since last Offer]@row >= 180, [# Days Since last Offer]@row = "No Offer Received"), "Auction")

All comments

Jeff Reisman

@french2s I'm not quite sure what you're asking for here, but maybe I can help.

First of all, #NO MATCH is an error message, so you can't just look for a cell = "#NO MATCH".

Let's call your columns ColumnA and ColumnB. You have a formula in ColumnA that could result in a #NO MATCH error. If that happens, you want ColumnB to equal "Auction". Is this about right? Try this in ColumnB:

=IFERROR(ColumnA@row, "Auction")

This says if there is any error message in ColumnA, set ColumnB to "Auction".

Now what I think you're asking with the "I need to combine it with the following" is that you want ColumnB to equal "Auction" if ColumnA is #NO MATCH OR if [# Days Since Last Offer]@row >= 180, right? We can do that! Try this:

=IF(OR([# Days Since Last Offer]@row >= 180, IF(IFERROR(ColumnA@row, 1) = 1, 1) = 1), "Auction")

french2s

Jeff- so here is what I am trying to do:

Column Name: # Days Since Last Offer - this returns either a number or #NO MATCH because there is nothing in the formula to subtract.

The Formula in say Column D should result in either 1) returned Auction - if the # days is greater than or equal to 180, OR 2) returning Auction if there is an error in that cell.

=IF(OR([# Days Since Last Offer]@row >= 180, IF(IFERROR(# Days Since Last Offer]@row, 1) = 1, 1) = 1), "Auction")

I tried this and it did not work.

Jeff Reisman

@french2s

Aha, okay we can do this a lot more easily then. You can use IFERROR wrapped around a formula to replace the error message with whatever you want. So take your formula in the # Days Since Last Offer column and wrap it in IFERROR, and give it some number value that clearly indicates to you a problem, like 9999 for example:

=IFERROR(formula currently in # Days Since Last Offer, 9999)

So now, the value in # Days Since Last Offer will either be below 180, or greater than/equal to 180, even if it would have resulted in a #NO MATCH before.

Now, in ColumnD:

=IF([# Days Since last Offer]@row >= 180, "Auction")

Done and done!

french2s

I'm not sure that will work.

Column: # Days Since Last Offer

Formula: =[Today's Date]@row - [Date Last Offer Received]@row

Results - either a number or #NO MATCH

That NO MATCH tells me that there has NEVER been an offer received, but I guess if it said 9999, that could work too.

New Formula: =IFERROR([Today's Date]@row - [Date Last Offer Received]@row, 9999)

I don't think that is right. I think I would need an IF AND statement so it would return the actual number or the 9999.

Jeff Reisman

@french2s

=IFERROR([Today's Date]@row - [Date Last Offer Received]@row, 9999) will absolutely work. You either get the result of the subtraction, or if it's an error you get 9999.

You could always use something other than 9999, such as "No Offer Received". Then you'd change you second formula to:

=IF(OR([# Days Since last Offer]@row >= 180, [# Days Since last Offer]@row = "No Offer Received"), "Auction")

Jeff Reisman

I think I would need an IF AND statement so it would return the actual number or the 9999.

The IFERROR function includes all of that, built into the function. Essentially, it says "run this formula and give me the result, unless the result is an error, in which case replace the error message with this other value." The other value can be text, number, blank text, or even another formula.

IFERROR(value, value_if_error)

value — The value, typically a formula, to test for an error
value_if_error — The value or formula to return if the first value results in an error

french2s

Thank you. I got it all to work. I greatly appreciate the help.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/337174#Comment_337174

Happy to help, glad it worked for you!

french2s

Jeff - what if I wanted to add another IF statement.

So here is the current and it works: =IF(OR([# Days Since Last Offer]@row >= 180, [# Days Since Last Offer]@row = "No Offer Received"), "Auction")

But if I wanted to add a statement that said BUT IF Column Name "Contract Status" = "Closed", THEN "Closed"

This is what I have but I think I am missing something:

=IF(OR([# Days Since Last Offer]@row >= 180, [# Days Since Last Offer]@row = "No Offer Received"), "Auction")), IF(OR[Contract Status]@row = "Closed", "Closed")

Thanks in Advance

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.