Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
IFERROR + IF/ISBLANK
This formula is almost perfectly pulling downtime by examiner and production date:
=IFERROR(INDEX(COLLECT({downtime - downtime}, {downtime - production date}, [Production Date]@row, {downtime - examiner}, Examiner@row), 1), 0)
There are a few records that are not in error, but the result is a blank, however, I need those blanks recorded as a zero. I have tried a number of ways to wrap this formula in an IF/ISBLANK with no success. I'm sure I'm missing something simple, but what is the trick to combining IF/ISBLANK/IFERROR?
At this point, I've added the above in a helper column. The downtime column with the IF/ISBLANK logic points to the helper, but I'd like to eliminate the helper column, if possible.
I have another sheet where I'm doing something similar and I need to note blank or error, so understanding this will help me with a few projects. Thanks in advance!
Best Answer
-
Try this:
=IF(IFERROR(INDEX(COLLECT({downtime - downtime}, {downtime - production date}, [Production Date]@row, {downtime - examiner}, Examiner@row), 1), 0) <> "", IFERROR(INDEX(COLLECT({downtime - downtime}, {downtime - production date}, [Production Date]@row, {downtime - examiner}, Examiner@row), 1), 0), "")
Answers
-
Try this:
=IF(IFERROR(INDEX(COLLECT({downtime - downtime}, {downtime - production date}, [Production Date]@row, {downtime - examiner}, Examiner@row), 1), 0) <> "", IFERROR(INDEX(COLLECT({downtime - downtime}, {downtime - production date}, [Production Date]@row, {downtime - examiner}, Examiner@row), 1), 0), "")
-
Thank you @Paul Newcome!
I made one minor change. I removed the last "" in favor of 0, which is what I need for this formula:
=IF(IFERROR(INDEX(COLLECT({downtime - downtime}, {downtime - production date}, [Production Date]@row, {downtime - examiner}, Examiner@row), 1), 0) <> "", IFERROR(INDEX(COLLECT({downtime - downtime}, {downtime - production date}, [Production Date]@row, {downtime - examiner}, Examiner@row), 1), 0), 0)In the same sheet we need to look up up the examiner, however, we need to know if the result is missing or blank, so I adapted the formula with this INDEX/MATCH. Being a little funny with "doesn't matter", but it really doesn't.
=IF(IFERROR(INDEX({examiner - xref}, MATCH([User Code]@row, {eldouser}, 0)), "doesn't matter") <> "", IFERROR(INDEX({examiner - xref}, MATCH([User Code]@row, {eldouser}, 0)), "cross reference missing"), "cross reference blank")Thanks for help with this sheet, but really thanks for putting it out here for everyone. I cannot be the only one to struggle with this one.
-
Thank you for sharing this. I was stuck on this on.
-
Thank you for sharing this. I was stuck on this one.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
