Multi-tiered pricing - Excel formula

[spypolice]

Greetings Friends,

I'm new to this forum and to complex Excel formulas in general. I've been searching through this very useful forum and have seen some examples of multi-tiered pricing that have been solved. However, I've not come across the problem I'm currently having and was wondering if I could get some help?

The goal is to have a cell where the user enters the number of users, and the price will be outputted in another cell.

I have the following pricing tiers:

• 50 user pack = $630
• 100 user pack = $1,240
• 500 user pack = $2,540
• 1,000 user pack = $4,515
• 3,500 user pack = $6,320
• 10,000 user pack = $9,480
• 25,000 user pack = $15,235

An example of the outcome should be:

- i.e. if user enters 49 in the input cell, then the price will be $630.
- i.e. if user enters 150 in the input cell, then the price will be $1,870. This is because the logic (excel formula) will pick the 50 user pack and the 100 user pack, rather than the 500 user pack
- i.e. if the user enters 15,000 in the input cell, then the price will be $15,235. This is because the 25,000 user pack will be cheaper than selecting the 10,000 user pack, 3,500 user pack, 1,000 user pack and the 500 user pack.

Hope this makes sense and appreciate the help!

Many thanks

Also asked here Multi-tiered pricing - Excel formula

 

[Sufiyan97]

An example of the outcome should be:

- i.e. if user enters 49 in the input cell, then the price will be $630.
- i.e. if user enters 150 in the input cell, then the price will be $1,870. This is because the logic (excel formula) will pick the 50 user pack and the 100 user pack, rather than the 500 user pack
- i.e. if the user enters 15,000 in the input cell, then the price will be $15,235. This is because the 25,000 user pack will be cheaper than selecting the 10,000 user pack, 3,500 user pack, 1,000 user pack and the 500 user pack.

- i.e. if user enters 150 in the input cell, then the price will be $1,870. This is because the logic (excel formula) will pick the 50 user pack and the 100 user pack, rather than the 500 user pack

What if when user enters 650? or any other number, you need to clarify each condition (I think) that's why you did not get any response on both of th forums becuase helpers are not getting exact idea on what you want to achieve.
Give some examples by creating a table and typing manual amounts.
and add comment for your logic, like why did you put that amount

- i.e. if the user enters 15,000 in the input cell, then the price will be $15,235. This is because the 25,000 user pack will be cheaper than selecting the 10,000 user pack, 3,500 user pack, 1,000 user pack and the 500 user pack.

What if when user enters anything between 3501 to 9999? which amount you want to return.

 

[spypolice]

- i.e. if user enters 150 in the input cell, then the price will be $1,870. This is because the logic (excel formula) will pick the 50 user pack and the 100 user pack, rather than the 500 user pack

What if when user enters 650? or any other number, you need to clarify each condition (I think) that's why you did not get any response on both of th forums becuase helpers are not getting exact idea on what you want to achieve.
Give some examples by creating a table and typing manual amounts.
and add comment for your logic, like why did you put that amount

- i.e. if the user enters 15,000 in the input cell, then the price will be $15,235. This is because the 25,000 user pack will be cheaper than selecting the 10,000 user pack, 3,500 user pack, 1,000 user pack and the 500 user pack.

What if when user enters anything between 3501 to 9999? which amount you want to return.

Apologies if I was not clearer (I thought I was with my examples). To answer your questions, the outcome should be whatever works out to be the cheapest for the customer.

The outcome I would like for your example of 650 users would be $4,410 (500 user pack + 100 user pack + 50 user pack). This is because picking out 3 user packs would be cheaper than the price of 1,000 user pack ($4,515).