Hi - Dave here.
Happy Friday!
The workbook below applies discounts based on seven pricing tiers. Each tier has a different unit price, which decreases as the quantity increases.
The tricky part of this problem is to calculate the correct quantities in the range D7:D13. Essentially, we need to distribute the value in C4 according to the thresholds in column C:
[
Read the article and download the workbook]
In the article, I explain two ways to solve this problem. One way is to use the MAP function to calculate all tier quantities in one step. This method requires Excel 365. The second way is to restructure the worksheet and use a more traditional formula that will work in all versions of Excel. Download the workbook and have a look yourself. Both approaches are included.
Note: I created this example to show how Excel's new MAP function can be used to solve tricky problems in an elegant way. Excel's new functions are very powerful, but it's often not clear how you might use a function like MAP. However, after I finished the traditional formula approach, I was reminded again how restructuring a worksheet can greatly simplify a problem. Do not overlook this "one simple trick" to make hard problems easier to solve :)
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at one useful Excel formula. You can sign up on our home page.