Combining IF Statement and IFERROR

french2s
french2s ✭✭
edited 07/21/22 in Formulas and Functions

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.

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 07/22/22 Answer ✓

    @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")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 07/21/22

    @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")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • 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
    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!

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • 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
    Jeff Reisman ✭✭✭✭✭✭
    edited 07/22/22 Answer ✓

    @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")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    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


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • 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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!