Skip to main content

Hi all! I’m looking for a solution in a switch formula, with nested if functions.


I want to calculate a percentage & addition based fee that changes based on the category input. Also with possible caveats on how that fee is calculated based on the price of the item. I’m hoping this is possible.


Example:

If Category=A, multiply by 12%

If Category=B, multiply by 9%, then add .50

If Category=C, AND the price is <$15, ADD $2, OR if Category=C, AND the price is >$14.99, multiply by 20%


Right now I have a separate table linked to include all this info, but I would love to set up a formula to calculate all of this within the main table. Thanks for the help!

Assuming I understood your question, the simplest possible formula is:


SWITCH(

   {Category},

   "A", {Price} * .12,

   "B", ({Price} * .09) + .5,

   "C", IF({Price} < 15, {Price} + 2, {Price} * .2)

)

Assuming I understood your question, the simplest possible formula is:


SWITCH(

   {Category},

   "A", {Price} * .12,

   "B", ({Price} * .09) + .5,

   "C", IF({Price} < 15, {Price} + 2, {Price} * .2)

)

Thank you! I kept working on it on case I didn’t get a response, and did figure out the solution, very similar to this. Really the only differences were I left out the parentheses in the ‘B’ category, since they’re unnecessary for the equation, and I typed out multiple “IF” statements for that “C” category. If you leave out the added "IF({Price}>=15, * .2}), does it essentially just make that second part of your statement the default if it’s not <15? I thought there was a way for that to happen to not have to type it all out, I just didn’t want to press my luck once I got it working :grinning_face_with_sweat: But I can see that being a lot cleaner.

Thank you so much for the response! That absolutely is the easiest formula and works!

Thank you! I kept working on it on case I didn’t get a response, and did figure out the solution, very similar to this. Really the only differences were I left out the parentheses in the ‘B’ category, since they’re unnecessary for the equation, and I typed out multiple “IF” statements for that “C” category. If you leave out the added "IF({Price}>=15, * .2}), does it essentially just make that second part of your statement the default if it’s not <15? I thought there was a way for that to happen to not have to type it all out, I just didn’t want to press my luck once I got it working :grinning_face_with_sweat: But I can see that being a lot cleaner.

Thank you so much for the response! That absolutely is the easiest formula and works!



Correct. The basic format of the IF() function is:


IF(condition, output_if_true, optional_output_if_false)

If {Price} < 15 isn’t true, then {Price} must be >= 15, so the third argument can be executed without the need for checking that condition again.


Correct. The basic format of the IF() function is:


IF(condition, output_if_true, optional_output_if_false)

If {Price} < 15 isn’t true, then {Price} must be >= 15, so the third argument can be executed without the need for checking that condition again.


Awesome, that makes total sense, thank you!

Reply


Terms & Conditions