Multi-tiered pricing - Excel formula

spypolice

New Member
Joined
Jun 6, 2018
Messages
3
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!
smile.gif


Many thanks

Also asked here Multi-tiered pricing - Excel formula
 
Last edited by a moderator:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
- 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).
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top