Pricing Sheet - If xx quantity, use this price

Originally Posted by Hendy_100

Hi Folks,

I am trying to create a staggered pricing model in excel and need a little leg up.

I want to enter the quantity into one cell and have a formula pull the price from a two-column table like below and return the price into a "total' cell. Seems easy enough, though I can't figure out how to make it differentiate the quantities and prices. Any help would be appreciated.

0-5000 $1.00
5000-6000 $0.90
6000-7000 $0.80
7000-8000 $0.70
8000-9000 $0.60
9000-10,000 $0.50
10,000-50,000 $0.40
50,000-100,000 $0.30
100,000-1,000,000 $0.20
1,000,000+ $0.10

Is this weeks homework on Lookup tables?

try http://www.excelforum.com/attachment...4&d=1162280508 from the post at http://www.excelforum.com/showthread.php?t=579258, this should explain how to do that.

hth
---

Here's a function you could use, check out the example attatched

Please Login or Register  to view this content.

Originally Posted by Hendy_100

Hi Folks,

I am trying to create a staggered pricing model in excel and need a little leg up.

I want to enter the quantity into one cell and have a formula pull the price from a two-column table like below and return the price into a "total' cell. Seems easy enough, though I can't figure out how to make it differentiate the quantities and prices. Any help would be appreciated.

0-5000 $1.00
5000-6000 $0.90
6000-7000 $0.80
7000-8000 $0.70
8000-9000 $0.60
9000-10,000 $0.50
10,000-50,000 $0.40
50,000-100,000 $0.30
100,000-1,000,000 $0.20
1,000,000+ $0.10

You can do this:

Enter the following from Cells A1 to A10:

0
5000
6000
7000
8000
9000
10000
50000
100000
1000000

On the corresponding cells in column B (B1 to B10), enter the following:

1
0.9
0.8
0.7
0.6
0.5
0.4
0.3
0.2
0.1

You can use this formula then to pull out the price that you require:

=vlookup(C1,$A$1:$B$10,2,2)

where C1 is where you enter the quantity.

If you want the total price based on the quantity, then use this formula:

=C1*(vlookup(C1,$A$1:$B$10,2,2))

Hope this helps you.

Regards.