Eliminating "0" from a cell when it is returned in a formula
Hi,
In order to get some downstream formulas to work, I am trying to remove "0" from my calculation cells if "0" is the result. Based on the reported success with the formula other Community posts, I have tried to use this approach:
=IF(original formula > 0, original formula)
...by using this sheet-specific formula:
=IF(((SUM([CONF1 - # of Leadership Roles]@row + [CONF1 - # of Committees]@row + [CONF2 - # of Leadership Roles]@row + [CONF2 - # of Committees]@row + [CONF3 - # of Leadership Roles]@row + [CONF3 - # of Committees]@row + [CONF4 - # of Leadership Roles]@row + [CONF4 - # of Committees]@row + [CONF5 - # of Leadership Roles]@row + [CONF5 - # of Committees]@row)),>0), (SUM([CONF1 - # of Leadership Roles]@row + [CONF1 - # of Committees]@row + [CONF2 - # of Leadership Roles]@row + [CONF2 - # of Committees]@row + [CONF3 - # of Leadership Roles]@row + [CONF3 - # of Committees]@row + [CONF4 - # of Leadership Roles]@row + [CONF4 - # of Committees]@row + [CONF5 - # of Leadership Roles]@row + [CONF5 - # of Committees]@row)))
However, I am getting a #UNPARSEABLE response. I've tried changing syntax and logic, rebracketing, etc... all to no avail. Any ideas on what I've done wrong in my formula?
Many thanks,
Walter
Best Answers
-
Try this...
=IF([CONF1 - # of Leadership Roles]@row + [CONF1 - # of Committees]@row + [CONF2 - # of Leadership Roles]@row + [CONF2 - # of Committees]@row + [CONF3 - # of Leadership Roles]@row + [CONF3 - # of Committees]@row + [CONF4 - # of Leadership Roles]@row + [CONF4 - # of Committees]@row + [CONF5 - # of Leadership Roles]@row + [CONF5 - # of Committees]@row > 0, [CONF1 - # of Leadership Roles]@row + [CONF1 - # of Committees]@row + [CONF2 - # of Leadership Roles]@row + [CONF2 - # of Committees]@row + [CONF3 - # of Leadership Roles]@row + [CONF3 - # of Committees]@row + [CONF4 - # of Leadership Roles]@row + [CONF4 - # of Committees]@row + [CONF5 - # of Leadership Roles]@row + [CONF5 - # of Committees]@row)
If this still does not work, double check the column names in the formula are an exact match to the column names in the sheet.
-
@Walter Grubb Try this...
=IF([H Index]@row > 0, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 1), 10, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.9), 9, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.8), 8, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.7), 7, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.6), 6, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.5), 5, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.4), 4, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.3), 3, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.2), 2, 1))))))))))
Basically we are saying that if [H Index]@row is greater than zero, run the original formula.
Answers
-
Try this...
=IF([CONF1 - # of Leadership Roles]@row + [CONF1 - # of Committees]@row + [CONF2 - # of Leadership Roles]@row + [CONF2 - # of Committees]@row + [CONF3 - # of Leadership Roles]@row + [CONF3 - # of Committees]@row + [CONF4 - # of Leadership Roles]@row + [CONF4 - # of Committees]@row + [CONF5 - # of Leadership Roles]@row + [CONF5 - # of Committees]@row > 0, [CONF1 - # of Leadership Roles]@row + [CONF1 - # of Committees]@row + [CONF2 - # of Leadership Roles]@row + [CONF2 - # of Committees]@row + [CONF3 - # of Leadership Roles]@row + [CONF3 - # of Committees]@row + [CONF4 - # of Leadership Roles]@row + [CONF4 - # of Committees]@row + [CONF5 - # of Leadership Roles]@row + [CONF5 - # of Committees]@row)
If this still does not work, double check the column names in the formula are an exact match to the column names in the sheet.
-
@Paul Newcome - that did the trick. Many thanks!!
I had a somewhat related Q on "excluding zero/empty cells" that perhaps you may have a suggestion. I have this formula below for assigning a decile value (1 through 10) to a dataset of numerical values, and it works really well. The trouble is that it includes all rows, meaning that rows with an empty value or a "0" in them are included in the deciling calculation. I've tried tinkering around a bit on ways to add qualifiers to the formula to exclude those cells, and only apply the deciling formula to cells with a ">0" value, but haven't had any luck yet. Am I missing something simple?... is there an "IF/AND" combination that I am missing to help qualify the formula?
=IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 1), 10, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.9), 9, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.8), 8, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.7), 7, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.6), 6, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.5), 5, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.4), 4, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.3), 3, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.2), 2, 1)))))))))
-
@Walter Grubb Try this...
=IF([H Index]@row > 0, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 1), 10, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.9), 9, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.8), 8, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.7), 7, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.6), 6, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.5), 5, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.4), 4, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.3), 3, IF([H-index]@row >= PERCENTILE([H-index]:[H-index], 0.2), 2, 1))))))))))
Basically we are saying that if [H Index]@row is greater than zero, run the original formula.
-
Thanks @Paul Newcome - this did the trick!! Don't know what I was doing wrong before... this is what I was going for but was clearly missing a syntax somewhere along the way. Much appreciate all the assistance!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!