asked on

Excel Formula to Calculate Tiered Pricing

I am struggling to write a set of formulas that produce the results in the attached spreadsheet and is based on the highlighted number entered. It should calculate the tiered cost for any number entered up to 200,000. I feel sure there is a concise and advanced set of formulas that will achieve the results, but I cant get there. I will need to use the logic quite often going forward.

Your help is greatly appreciated.

The corresponding Excel workbook is attached.
Pricing-Example.xlsx

Rgonzo1971

HI,

pls try example in G5

=(MIN(5000,D3)*0.96)+MAX(0,(MIN(5000,D3-5000)*0.92))+MAX(0,(MIN(10000,D3-10000)*0.9))+MAX(0,(MIN(10000,D3-20000)*0.85))+MAX(0,(MIN(10000,D3-30000)*0.8))+MAX(0,(MIN(10000,D3-40000)*0.75))+MAX(0,(MIN(10000,D3-50000)*0.7))+MAX(0,(MIN(10000,D3-60000)*0.65))+MAX(0,(MIN(10000,D3-70000)*0.6))+MAX(0,(MIN(120000,D3-80000)*0.55))

Open in new window

EDIT the Version1 has the formula distributed over E5:E14

Regards
Copy-of-Pricing-Example.xlsx
Copy-of-Pricing-ExampleV1.xlsx

SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mcarsonsr

ASKER

Both solutions work and thank you. What is the protocol regarding point distribution? Rgonzo1971 solution came through first, but want to be fair. Just not sure how to assign the points in this situation.

Thank you valuable assistance!