Skip to main content
Solved

Need Help Creating IF Or And Switch Statement

  • April 23, 2020
  • 2 replies
  • 24 views
J
Forum|alt.badge.img+4

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!

Best answer by Jeremy_Oglesby

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.

    2 replies

    J
    Forum|alt.badge.img+18

    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.

    J
    J
    Forum|alt.badge.img+4
    • Juliet_S
    • Author
    • New Participant
    • April 23, 2020

    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.

    J
    Terms & ConditionsAccessibility statement