Help

Creating Conditional Formula

Topic Labels: Formulas
Solved
Jump to Solution
682 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Kaitlynn_Carr
4 - Data Explorer
4 - Data Explorer

I have a unique situation that I couldn’t find an answer to, possibly to my lack of patience. But I am trying to create a pricing sheet of products. I have two manufacturers. I have installer and wholesale accounts that only get a discount on one of the manufacturers’ products. I was trying to come up with a formula that calculated the accounts discount automatically since I change prices every so often, but want it conditional to the one manufacturer it applies to.

I need it to be that if the manufacturer cell says SCF, the installer sell is retail * .9, and wholesale is retail*.75 and if the manufacturer cell says Maven, installer and wholesale are both same as retail.

Is this possible???

(I tried attaching a screenshot of my table, and it’s not letting me so I’m explaining it as best as I can)

1 Solution

Accepted Solutions
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

Hi @Kaitlynn_Carr – this is definitely possible. I’ll take a stab and just guess at your field names. Anything enclosed in { } needs to be replaced with your actual field names.

This is for {Installer}

IF(
  {Manufacturer},
  SWITCH(
    {Manufacturer},
    "SCF", {Retail} * 0.9,
    "Maven", {Retail}
  )
)

This is for {Wholesale}

IF(
  {Manufacturer},
  SWITCH(
    {Manufacturer},
    "SCF", {Retail} * 0.75,
    "Maven", {Retail}
  )
)

Just format these fields as “Currency” and this should get you what you are looking for. Both of these formulas first check for the presence of a value (any value) in the {Manufacturer} field before outputting anything. Then, they use a SWITCH() function that looks at the value of the {Manufacturer} field and processes a different equation based on its value.

Perhaps I’ve oversimplified what you are needing done – if so, just respond back with what’s lacking.

See Solution in Thread

2 Replies 2
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

Hi @Kaitlynn_Carr – this is definitely possible. I’ll take a stab and just guess at your field names. Anything enclosed in { } needs to be replaced with your actual field names.

This is for {Installer}

IF(
  {Manufacturer},
  SWITCH(
    {Manufacturer},
    "SCF", {Retail} * 0.9,
    "Maven", {Retail}
  )
)

This is for {Wholesale}

IF(
  {Manufacturer},
  SWITCH(
    {Manufacturer},
    "SCF", {Retail} * 0.75,
    "Maven", {Retail}
  )
)

Just format these fields as “Currency” and this should get you what you are looking for. Both of these formulas first check for the presence of a value (any value) in the {Manufacturer} field before outputting anything. Then, they use a SWITCH() function that looks at the value of the {Manufacturer} field and processes a different equation based on its value.

Perhaps I’ve oversimplified what you are needing done – if so, just respond back with what’s lacking.

That worked perfectly! Thank you so so much!