Help with Excel Formula

[manshah]

I’m running into a scenario where I need help with formula in excel.

Here is the situation:
I have a product pricing work book where client will select the product they want to buy and it shows the list price in column labeled "List Price " in the image below. Client will also select the currency they would like to use to buy the products. Based on the currency selection list prices will update in "list price" column. .

If they select more than one product, than following discounted pricing should be applied.

For USD:

First Product : Full retail price (price reflected in column G)

Second Product: $90 (flat)

Third Product: $70 (flat)

Fourth Product: $50 (flat

Fifth Product: $50 (flat)

And so on until the total price max out at $650. Once it reaches $650 it does not matter how many products they selected (it could be 5 or 15), price will stay at $650.

Below is the discount pricing table for each currency.

	​
	US Dollars​	CAN	GBP	Euros
First Product	Full retail price​	Full retail price​ ​	Full retail price​ ​	Full retail price​ ​
Second Product	90​	110​	70​	80​
Third Product	70​	90​	60​	60​
Fourth Product	50​	60​	40​	50​
Fifth Product	50​	60	40	50
Sixth	50​	60	40	50
Seventh	50​	60	40	50
So on
Max	650​	815​	560​	510​

At the end I would like to show them the table which list all the products selected with their list price and discounted price

End Result Example:

For US Dollars

Product Summary (1 audience)
	List Price	Discounted Price
Product 1	$ 340	$ 340.00
Product 2	$ 255.00	$ 90
Product 3	$ 220.00	$ 70
Product 4	$ 170.00	$ 50
Product 5	$ 85.00	$ 50
Product 6	$ 80.00	$ 50
Product 7	$ 70.00	$ -
Product 8	$ 55.00	$ -
Product 9	$ 50.00	$ -
Product 10	$ 45.00	$ -
Product 11	$ 35.00	$ -
Product 12	$ 30.00	$ -
Total	$ 1,435.00	$650​

I worked on the formulas and tried to achieve above result in attached workbook in cells AC9 through AE23. But sometimes it works and sometimes it does not since product 1 list price will be different.

Any work around to that problem? Any formula that will help to achieve this result?

Your help will be greatly appreciate.

Thank you.

 

[Rows N Columns]

Hi, I tried to replicate a simplified version of your data. Please see below steps I took:
1. Add discount table as a reference table in columns I to M.

2. Compute the maximum discounted price in column F by referencing the discount table. This will change according to the currency selected in Cell C1, and also the product S/N in column A.

=IF($A4=1,$C4,IF($A4>=4,INDEX($J$6:$M$12,4,MATCH($C$1,$J$4:$M$4,0)),INDEX($J$6:$M$7,MATCH($A4,$I$6:$I$7,0),MATCH($C$1,$J$4:$M$4,0))))

3. Compute the cumulative max discounted price in column G

4. Compute the discounted price in column D which gets the discounted price from Column F, but once the cumulative value in column G reaches or exceeds the Max discount for the selected currency, it will show 0

=IF(G4<=INDEX($J$13:$M$13,1,MATCH($C$1,$J$4:$M$4,0)),F4,0)

In summary, Columns B, C and D would be what you show your customer, while Columns F and G are helper columns to assist in the calculation of Column D.

Is this what you need?