How to calculate price based on category of item and their tier level

I’m trying to calculate an amount based on an item, and a tier level.
The current Expression I have is this:
IF(AND([Tier]="Affiliate Partner",[Category]="Demo Completed"),"25",IF(AND([Tier]="Affiliate Partner",[Category]="License Purchased"),"50",IF(AND([Tier]="Affiliate Partner",[Category]="Documents Migrated"),"100",IF(AND([Tier]="Certified Partner",[Category]="Demo Completed"),"25",IF(AND([Tier]="Certified Partner",[Category]="License Purchased"),([Amount]/10),IF(AND([Tier]="Certified Partner",[Category]="Documents Migrated"),([Amount]/10),IF(AND([Tier]="Gold Partner",[Category]="Demo Completed"),"25",IF(AND([Tier]="Gold Partner",[Category]="License Purchased"),([Amount]/10),IF(AND([Tier]="Gold Partner",[Category]="Documents Migrated"),([Amount]/10),"")))))))))
What am I doing wrong? Is there a different expression I should use?

In what way doesn’t your expression work?

My expression doesn’t calculate everything it should. It leaves the value blank for any category other than “Demo Completed”.

Here’s your expression reformatted to my preference:

IF(
  AND(
    [Tier]="Affiliate Partner",
    [Category]="Demo Completed"
  ),
  "25",
  IF(
    AND(
      [Tier]="Affiliate Partner",
      [Category]="License Purchased"
    ),
    "50",
    IF(
      AND(
        [Tier]="Affiliate Partner",
        [Category]="Documents Migrated"
      ),
      "100",
      IF(
        AND(
          [Tier]="Certified Partner",
          [Category]="Demo Completed"
        ),
        "25",
        IF(
          AND(
            [Tier]="Certified Partner",
            [Category]="License Purchased"
          ),
          ([Amount]/10),
          IF(
            AND(
              [Tier]="Certified Partner",
              [Category]="Documents Migrated"
            ),
            ([Amount]/10),
            IF(
              AND(
                [Tier]="Gold Partner",
                [Category]="Demo Completed"
              ),
              "25",
              IF(
                AND(
                  [Tier]="Gold Partner",
                  [Category]="License Purchased"
                ),
                ([Amount]/10),
                IF(
                  AND(
                    [Tier]="Gold Partner",
                    [Category]="Documents Migrated"
                  ),
                  ([Amount]/10),
                  ""
                )
              )
            )
          )
        )
      )
    )
  )
)

Here’s the expression using IFS() rather than IF():

IFS(
  AND(
    [Tier]="Affiliate Partner",
    [Category]="Demo Completed"
  ),
    "25",
  AND(
    [Tier]="Affiliate Partner",
    [Category]="License Purchased"
  ),
    "50",
  AND(
    [Tier]="Affiliate Partner",
    [Category]="Documents Migrated"
  ),
    "100",
  AND(
    [Tier]="Certified Partner",
    [Category]="Demo Completed"
  ),
    "25",
  AND(
    [Tier]="Certified Partner",
    [Category]="License Purchased"
  ),
    ([Amount]/10),
  AND(
    [Tier]="Certified Partner",
    [Category]="Documents Migrated"
  ),
    ([Amount]/10),
  AND(
    [Tier]="Gold Partner",
    [Category]="Demo Completed"
  ),
    "25",
  AND(
    [Tier]="Gold Partner",
    [Category]="License Purchased"
  ),
    ([Amount]/10),
  AND(
    [Tier]="Gold Partner",
    [Category]="Documents Migrated"
  ),
    ([Amount]/10)
)

Or with SWITCH():

SWITCH(
  [Tier],
  "Affiliate Partner",
    SWITCH(
      [Category],
      "Demo Completed", "25",
      "License Purchased", "50",
      "Documents Migrated", "100",
      ""
    ),
  "Certified Partner",
    SWITCH(
      [Category],
      "Demo Completed", "25",
      "License Purchased", ([Amount]/10),
      "Documents Migrated", ([Amount]/10),
      ""
    ),
  "Gold Partner",
    SWITCH(
      [Category],
      "Demo Completed", "25",
      "License Purchased", ([Amount]/10),
      "Documents Migrated", ([Amount]/10),
      ""
    )
)

I notice you’re mixing numeric and textual values, which isn’t a good idea. I suspect you mean the result to be a numeric value. If so, the result values should not be in quotes.

1 Like

I ended up figuring it out, Steve! I had to convert the category column to text first. Thank you for the help, and for the alternate expression types.

1 Like