Help

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

Topic Labels: Formulas
Solved
Jump to Solution
953 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Mindy_Foskey
4 - Data Explorer
4 - Data Explorer

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})

image

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!

1 Solution

Accepted Solutions
Mohamed_Swella1
13 - Mars
13 - Mars

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

See Solution in Thread

3 Replies 3
Mohamed_Swella1
13 - Mars
13 - Mars

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

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!

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})