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

Options

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

Tags:

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    @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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

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

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!