Varing cost based on different multi select choices

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

Hi

I want to write a formula to calculate different costs based on the choices made in a multiselect dropdown.

I have 3 fields : Customer (multi select text), Total Cost (numeric currency), Cost to Customer (numeric - formula)

The formula in the Cost to Customer field needs to read :

If only Customer 1 (or only customer 2 or only Customer 3) is selected in Customer then return Total Cost,

if Customer 1 and Customer 2 are selected or Customer 1 and Customer 3 or Customer 2 and Customer 3) in Customer then return Total Cost divided by 2,

if Customer 1, Customer 2 and Customer 3 are selected in Customer then return Total Cost divided by 3

This formula will only apply if 3 specific customers are selected out of a multi-select list of 10 customers.  It will not apply to the other 7 in the multi-select dropdown.

Any thoughts?

Thanks

Sarah

Photo of Sarah

Sarah

  • 20 Points

Posted 3 years ago

  • 0
  • 1
Try this (not tested)

var text Customer = ToText(Customer]);

var bool CustOne = Contains($Customer, "Larry");

var bool CustTwo = Contains($Customer, "Curly");


var bool CustThree = Contains($Customer, "Moe");


var number Shares = Count($CustOne, $CustTwo, $CustThree)

var number DivideBy = IF($Shares >=2, $Shares,1)

[Total Cost] / $DivideBy