SWITCH() Function for Custom Mark up

[Josh_Klassen]

Hi everyone, 

I am trying to create a formula that marks up my parts based on the sell price per unit. 

For example I have a mark up based on different price $ 

$0-100 is 2.0

$100-$500 is 1.75

$500 - $1000.00 is 1.5

$1000.00 - $200,000.00 is 1.33

I am trying to get the app change a value in a column based off of what the sell price per unit is.  So for example if I sell a banana for $50.00 then the value of the custom mark up formula  column would be 2.0 but if I sold a computer for $750.00 then the value of the custom mark up formula column would be 1.5 

I have a formula of 

SWITCH([Custom Mark Up Formula],

AND(
([Sell Price Per Unit] >= [If part is between 1]),
([Sell Price Per Unit] <= [and 1])),
[Mark up 1],

AND(
([Sell Price Per Unit] >= [and 1]),
([Sell Price Per Unit] <= [and 2])),
[mark up 2],

AND(
([Sell Price Per Unit] >= [and 2]),
([Sell Price Per Unit] <= [and 3])),
[mark up 3],

AND(
([Sell Price Per Unit] >= [and 3]),
([Sell Price Per Unit] <= [and 4])),
[mark up 4],

AND(
([Sell Price Per Unit] >= [and 4]),
([Sell Price Per Unit] <= [and 5])),
[mark up 5],

AND(
([Sell Price Per Unit] >= [and 5]),
([Sell Price Per Unit] <= [and 6])),
[mark up 6],
"5.0") 

Which doesn't work... it returns 5.0 as the default value no matter what the value of the column sell price per unit is. 

Can someone please help with this. 

Thanks! Josh 

[dbaum]

That's not how SWITCH works. I don't follow your expression, but likely you instead need IFS.

[Sirfyaad]

try this

SWITCH(
  TRUE,
  AND(
    [Sell Price Per Unit] >= [If part is between 1],
    [Sell Price Per Unit] <= [and 1]
  ),
  [Mark up 1],
  
  AND(
    [Sell Price Per Unit] >= [and 1],
    [Sell Price Per Unit] <= [and 2]
  ),
  [mark up 2],
  
  AND(
    [Sell Price Per Unit] >= [and 2],
    [Sell Price Per Unit] <= [and 3]
  ),
  [mark up 3],
  
  AND(
    [Sell Price Per Unit] >= [and 3],
    [Sell Price Per Unit] <= [and 4]
  ),
  [mark up 4],
  
  AND(
    [Sell Price Per Unit] >= [and 4],
    [Sell Price Per Unit] <= [and 5]
  ),
  [mark up 5],
  
  AND(
    [Sell Price Per Unit] >= [and 5],
    [Sell Price Per Unit] <= [and 6]
  ),
  [mark up 6],
  
  "5.0"
)