Tiered discounts based on quantity (formula)

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: [A workbook to calculated tiered discounts based on quantity.]( [[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](. [View this email in a browser]( [Exceljet Logo]( Exceljet P.O. Box 4804 Salt Lake City, UT 84110 Copyright © 2024 Exceljet, All rights reserved. You received this email because you are subscribed to our newsletter. To unsubscribe, click the link below. [Unsubscribe](