Formula to return pricing from a matrix based on Qty, but allow overrides

[Mindy_Foskey]

Hi,
I’m writing an item order table and have been successful in pulling prices from a matrix based on qty input using the Tiered system, but I find I now need to include the ability to override the price.

The Tier Formula is below and works - it populates the name of the tier based on the quantity input
IF(Quantity > 1, IF(Quantity < 5, “Tier 2”,
IF(Quantity >= 5, IF(Quantity < 10, “Tier 3”,
IF(Quantity >= 10, IF(Quantity < 15, “Tier 4”,
IF(Quantity >= 15, IF(Quantity < 25, “Tier 5”,
IF(Quantity >= 25, “Bulk”)))))))), “single”)

Once the tier is populated, I’m using a SWITCH formula to pull in the correct price based on the price tier
SWITCH((Tier),
“Single”,{Single Unit Cost},
“Tier 2”,{2-4 Units Cost},
“Tier 3”,{5-9 Units Cost},
“Tier 4”,{10-14 Units Cost},
“Tier 5”,{15-24 Units Cost},
“Bulk”, {Bulk 25+ Plus})

I find that I need to be able to override a price if their is a special one off price for a given line item. But since I’m using a formula, there doesn’t seem to be a way to override a price pulled from the matrix. I’ve hit a wall. Any suggestions for writing this missing piece would be most appreciated. Thanks in advance!

[Mohamed_Swella1 · Accepted solution]

Hi @Mindy_Foskey,

Welcome to Airtable Community! :slightly_smiling_face:

What I did before to solve this issue was add a Price Override Field and include it in the formula for the Total Cost.

So the formula for the total cost would become

IF({Price Override}, {Price Override} * Quantity, {Price per unit} * Quantity)

This will leave everything as it currently is unless you enter a value in the price override.

If this helps, please mark it as answered so others can see it.

BR,
Mo

[Mindy_Foskey]

Mohamed_Swellam:

IF({Price Override}, {Price Override} * Quantity, {Price per unit} * Quantity)

Hi Mohamed,

Thank you so much for the solution, it worked perfectly. Appreciate the fresh set of eyes and perspective to solve this issue. Thank you again!

[Justin_Barrett]

Mohamed_Swellam:

IF({Price Override}, {Price Override} * Quantity, {Price per unit} * Quantity)

Here’s an alternate way to write this. Because the only thing actually changing is the value by which {Quantity} will be multiplied, you could wrap the IF() just around that part:

Quantity * IF({Price Override}, {Price Override}, {Price per unit})