Iferror challenge
Hi, I'm trying to set up a formula to return the following information:
If the "Pad Possession" (a date) and the "Fixturing Period" (a number) both exist, then add the number of days to the date and return the new date.
If either or both of the Pad Possession or Fixturing Period cells are blank, then return "Awaiting Possession".
I used the "IFERROR" formula (pasted below), which works if either the Pad Possession or Fixturing Period cells are blank, but if both are blank then there is no error, and the formula returns nothing.
=IFERROR([PAD Possession Done / A&W Fixturing Started]@row + [Fixturing Period (Days)]@row, "Awaiting Possession")
Can anyone suggest how I can make this work? Thank you!
Answers
-
Strange - I see a different behavior:
Using =IFERROR(Pad@row + Period@row, "Awaiting Possession") I get the error if they're both blank; if there's a [Period] but no [Pad] the result is a blank.
In any case, would this work? It's a little brute-force-y but maybe more predicable...
=IF(AND(NOT(ISBLANK(Pad@row)), NOT(ISBLANK(Period@row))), Pad@row + Period@row, "Awaiting Posession")
Gives me:
Aaron
-
Thanks Aaron. I see in principle how your formula will work. I copied and paste the formula into my sheet, changing the references. However, I'm getting a circular reference error. Any suggestions?
-
Hi @A&W Admin
Can you clarify what column you're pasting the formula in to? Would you be able to post a screen capture of your sheet (but please block out sensitive data)?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
This is my formula
=IF(AND(NOT(ISBLANK([PAD Possession Done / A&W Fixturing Started]@row)), NOT(ISBLANK([Fixturing Expiry]@row))), [Fixturing Expiry]@row + [PAD Possession Done / A&W Fixturing Started]@row, "Awaiting Possession")
-
Hi @A&W Admin
Thank you for the image! I can see that you're pasting the formula into the "Fixturing Expiry" column, but you're referencing this column in the formula, which is why you're getting an error.
=IF(AND(NOT(ISBLANK([PAD Possession Done / A&W Fixturing Started]@row)), NOT(ISBLANK([Fixturing Expiry]@row))), [Fixturing Expiry]@row + [PAD Possession Done / A&W Fixturing Started]@row, "Awaiting Possession")
I believe that instead of using [Fixturing Expiry] you were intending to use the [Fixturing Period (days)] column. Here's the same formula @AaronO suggested above, using your column names:
=IF(AND(NOT(ISBLANK([PAD Possession Done / A&W Fixturing Started]@row)), NOT(ISBLANK([Fixturing Period (Days)]@row))), [PAD Possession Done / A&W Fixturing Started]@row + [Fixturing Period (Days)]@row, "Awaiting Posession")
Let me know if that makes sense and works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you so much! Part of the challenge for me was getting the column to reference correctly in the formula (our sheet has way too many columns so I was trying to scroll back and forth). I ended up moving the formula column right beside the columns I am referencing, and then referencing the columns was a breeze! I appreciate your patience and help. Thank you again.
-
Glad to hear you sorted it out! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!