Using IF and Symbols

Nate420

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

Jeff Reisman

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

Jeff Reisman

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

Dan W

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

Thank you!! Worked great

Dan W