Skip to main content
Solved

Creating Conditional Formula

  • May 21, 2020
  • 2 replies
  • 28 views
K

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)

Best answer by Jeremy_Oglesby

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.

2 replies

J
Forum|alt.badge.img+18

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.

K
  • Kaitlynn_Carr
  • Author
  • New Participant
  • May 21, 2020

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!

J
Terms & ConditionsAccessibility statement