Skip to main content

Hello, I have a table and need to assign a price depending on the status AND the Language:


    {Status} = Confirmed & {Language} = Spanish Then $95

	{Status} = Zoom & {Language} = Spanish then $85

	{Status} = cxl on time then $0

	{Status} = Late cxl & {Language} = Spanish then $95

	{Status} = Zoom & {Language} = X-Exotic then $115

	{Status} = Confirmed & {Language} = X-Exotic then $150

	{Status} = No Show & Zoom & {Language} = Spanish Then $95

	{Status} = No Show & Zoom & {Language} = Exotic Then $115

Thanks in advance!

Hi @Juliet_S,


Since each one of those seems to have mutually exclusive conditions to the others, I think you could make each one its own conditional statement and concatenate them within the same formula field.


IF(

  AND(

    Status = 'Confirmed',

    Language = 'Spanish'

  ),

  95

) &

IF(

  AND(

    Status = 'Zoom',

    Language = 'Spanish'

  ),

  85

) &

IF(

  Status = 'cxl on time',

  0

) &

IF(

  AND(

    Status = 'Late cxl',

    Language = 'Spanish'

  ),

  95

) &

IF(

  AND(

    Status = 'Zoom',

    Language = 'X-Exotic'

  ),

  115

) &

IF(

  AND(

    Status = 'Confirmed',

    Language = 'X-Exotic'

  ),

  150

) &

IF(

  AND(

    Status = 'No Show & Zoom',

    Language = 'Spanish'

  ),

  95

) &

IF(

  AND(

    Status = 'No Show & Zoom',

    Language = 'Exotic'

  ),

  115

)

There may be clever ways to condense that by using SWITCH() functions and nested IF() functions, but I think this should work, and I think it’s probably the most straight-forward, and easily digestible presentation of your conditions.

Hi @Juliet_S,


Since each one of those seems to have mutually exclusive conditions to the others, I think you could make each one its own conditional statement and concatenate them within the same formula field.


IF(

  AND(

    Status = 'Confirmed',

    Language = 'Spanish'

  ),

  95

) &

IF(

  AND(

    Status = 'Zoom',

    Language = 'Spanish'

  ),

  85

) &

IF(

  Status = 'cxl on time',

  0

) &

IF(

  AND(

    Status = 'Late cxl',

    Language = 'Spanish'

  ),

  95

) &

IF(

  AND(

    Status = 'Zoom',

    Language = 'X-Exotic'

  ),

  115

) &

IF(

  AND(

    Status = 'Confirmed',

    Language = 'X-Exotic'

  ),

  150

) &

IF(

  AND(

    Status = 'No Show & Zoom',

    Language = 'Spanish'

  ),

  95

) &

IF(

  AND(

    Status = 'No Show & Zoom',

    Language = 'Exotic'

  ),

  115

)

There may be clever ways to condense that by using SWITCH() functions and nested IF() functions, but I think this should work, and I think it’s probably the most straight-forward, and easily digestible presentation of your conditions.


Thank you! I have tested and it works well.

Reply


Terms & Conditions