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

[Binkodyl]

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?

[Steve]

In what way doesn’t your expression work?

[Binkodyl]

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

[Steve]

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.

[Binkodyl · Accepted solution]

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.