Tiered Pricing Formula Help

[jbonsignore]

Hi, I need some help with a tiered pricing formula. I've looked through several threads but haven't found anything that fits my need.

Per the attached spreadsheet, I need a formula that will provide a the total price as follows...

Number of Users:

Number of Users Price Total
Up to 250 $840
251-500 $756
501-1,000 $672
1,001-2,000 $552

If 1,000 users is entered, a price of $735,000 should display in the 'Total' cell on the 501-1000 line. The first 250 users * $840, the next 250 users * $756, and the last 500 users * $672 for a total price of $735,000. A $0 amount should be displayed in the rest of the 'Total' cells.


Thanks in advance for any help you can provide!!!

[tim201110]

=(e1-vlookup(e1,$a$1:$c$4,1))*vlookup(e1,$a$1:$c$4,2)+vlookup(e1,$a$1:$c$4,3)

[jtakw]

Hi,

Don't see your attached spreadsheet, based on my sample attached, formula in F2 (copied down if needed):

Please Login or Register  to view this content.

[jbonsignore]

is it possible for the cell to show the range of users instead of just the bottom value? i.e. 1-250, 251-500, etc.

[jtakw]

Not sure what you mean, perhaps you should upload a sample with your layout and expected results.

[jbonsignore]

I'm unable to attached my spreadsheet. Can you advise on how to attach/upload? Thanks!

[jtakw]

In a Reply post, or Edit your OP, lower right corner, "Go Advanced", scroll down, "Manage Attachments", browse for your file, Upload, Submit reply or Save changes.

[jbonsignore]

Spreadsheet setup as shown below.

A______________B____________C
1___Number of users: xx
2
3____Number of Users_____Price_________Total
4____Up to 250_________$840___________$0
5____251-500__________$756___________$0
6____501-1,000_________$672__________$0
7____1,001-2,000_______$552___________$0

[jbonsignore]

Spreadsheet attached, I hope.

[jtakw]

What happens After 2000? Right now, the formula Caps Out at 2000, no additional pricing is added after 2000.

Based on my modification of your uploaded sample, formula in I4 copied down to I7, see attached.

Please Login or Register  to view this content.

[jbonsignore]

I've attached an updated spreadsheet. My formula is partially working as desired. However, the only Price' column I want populated is the price cell that number of users falls into. The rest of the price cells should equal $0. Can someone help with this final piece?

Thanks!