Using IF and Symbols

Options
Nate420
Nate420 ✭✭
edited 06/28/22 in Formulas and Functions

Hello,

I would like to take the current usage number of a product and depending on a trend line symbol either return the current usage number, add 10% to it or minus 10% from it.

=IF([Trend Line]@row = "unchanged", [Current Average Monthly Usage (Kg)]@row), [Trend Line]@row = "up", [Current Average Monthly Usage (Kg)]@row * 0.1)

This is what I was thinking but its not working. I am also not sure how I would minus 10% of a number if the trend line is "down".

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @Nate420

    You'll want to use nested IFs to achieve this. The syntax for nested IFs is:

    IF(logical expression, value if true, IF(logical expression, value if true, IF(logical expression, value if true, optional value if false)))

    Essentially each subsequent IF exists as the value if false of the previous one. Remember to close them all at the end with matching close parentheses.

    To add 10% for your "up", multiply the value by 1.1. To subtract 10% for your "down", multiply the value by .9.

    =IF([Trend Line]@row = "unchanged", [Current Average Monthly Usage (Kg)]@row), IF([Trend Line]@row = "up", [Current Average Monthly Usage (Kg)]@row * 1.1, IF([Trend Line]@row = "down", [Current Average Monthly Usage (Kg)]@row * .9)))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @Nate420

    You'll want to use nested IFs to achieve this. The syntax for nested IFs is:

    IF(logical expression, value if true, IF(logical expression, value if true, IF(logical expression, value if true, optional value if false)))

    Essentially each subsequent IF exists as the value if false of the previous one. Remember to close them all at the end with matching close parentheses.

    To add 10% for your "up", multiply the value by 1.1. To subtract 10% for your "down", multiply the value by .9.

    =IF([Trend Line]@row = "unchanged", [Current Average Monthly Usage (Kg)]@row), IF([Trend Line]@row = "up", [Current Average Monthly Usage (Kg)]@row * 1.1, IF([Trend Line]@row = "down", [Current Average Monthly Usage (Kg)]@row * .9)))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Dan W
    Dan W ✭✭✭✭✭
    Options

    You can nestle another IF in the Value if false section. Like so

    =IF([Trend Line]@row = "unchanged", [Current Average Monthly Usage (Kg)]@row, IF([Trend Line]@row = "Up", [Current Average Monthly Usage (Kg)]@row * 0.1 + [Current Average Monthly Usage (Kg)]@row, IF([Trend Line]@row = "Down", -[Current Average Monthly Usage (Kg)]@row * 0.1 + [Current Average Monthly Usage (Kg)]@row)))


    Also you would want to add the percentage by doing +or- [Current Average Monthly Usage (Kg)]@row * 0.1 + [Current Average Monthly Usage (Kg)]@row like I did in the formula above.

  • Nate420
    Nate420 ✭✭
    Options

    Thank you!! Worked great

  • Dan W
    Dan W ✭✭✭✭✭
    edited 06/28/22
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!