Using IF and Symbols
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

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

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!

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.

Thank you!! Worked great

Help Article Resources
Categories
Check out the Formula Handbook template!