# Pricing Sheet - If xx quantity, use this price

1. ## Pricing Sheet - If xx quantity, use this price

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

2. 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
---

3. ## UDF Price per QTY

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

``Please Login or Register  to view this content.``

4. 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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1