Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

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

Topic Labels: Formulas
Solved
Jump to Solution
518 3
cancel
Showing results for
Search instead for
Did you mean:
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})

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
13 - Mars

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

3 Replies 3
13 - Mars

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

4 - Data Explorer

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