Skip to main content
Solved

Switch with nested if formula

  • July 10, 2021
  • 4 replies
  • 283 views
A
Forum|alt.badge.img+1

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!

Best answer by Kamille_Parks11

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

4 replies

Kamille_Parks11
Forum|alt.badge.img+27

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)
)
A
Justin_Barrett
A
Forum|alt.badge.img+1
  • April_Kastner
  • Author
  • Known Participant
  • July 10, 2021

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!

Justin_Barrett
Forum|alt.badge.img+21

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.

A
Forum|alt.badge.img+1
  • April_Kastner
  • Author
  • Known Participant
  • July 10, 2021

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!

Terms & ConditionsAccessibility statement