Eliminating "0" from a cell when it is returned in a formula

Walter Grubb

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

Paul Newcome

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

@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.

Paul Newcome

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

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

Paul Newcome

@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.

Walter Grubb

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!

Paul Newcome

Happy to help. 👍️