Multiple IF statements

Platform discussions Special workflows & use cases
1

Hello! The current formula multiplies the Quantity column by the Rate column and adds the number in the .01% of Asset Value column. A zero in the Quantity column will automatically spit out a zero in the Total column.

There is only one problem: I want the formula to account for a negative number in the Quantity column. If the Quantity column contains a negative number, it should still multiply the Quantity by the Rate, but then it should also subtract the number in the .01% of Asset Value column. Can anyone help me figure out this formula? Assuming it would just be another IF statement.

Current Formula (doesn’t account for negative number in the Quantity column:
IF(({Quantity} + 0) = 0, 0, ({Rate} * {Quantity}) + {.01% of Asset Value})

2

@AriellaD

You could do it by adding another IF(). I think it’s simpler if you don’t use any.

Give this a try:
({Rate} * {Quantity}) + (SIGN({Quantity}) * {.01% of Asset Value})

Jim - The Monday Man

:fire:NEW:fire:
:magic_wand: Update Magic #1 :sparkles:-The Magical Updates app

3

That worked! I just removed SIGN bc it was throwing off the formula a little. What was the purpose of the SIGN function here?

4

@AriellaD

SIGN() returns 1 for positive numbers, -1 for negative numbers and 0 for 0.

It is critical to make your function work as you described for {Quantity} values less or equal to 0.

Can you describe what you mean that it was “throwing off the formula a little”?