Combining IF Statement and IFERROR
![french2s](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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
-
=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
-
@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.
-
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.
-
=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!
-
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.
-
Happy to help, glad it worked for you!
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!