Please Help: Order Form Calculations

[Ed]

Hoping someone can help me. New to this group, so forgive me if I don't ask
the right questions.
I need to do an order form that handles 'price breaks' without having a
hundred rows of data.
For example.
Product 1
1 - 4 = $50 each
5 - 9 = $40 each
10 - 15 = $30 each
(there are 11 price breaks in all)
------------------
Product 2
1 - 4 = $65 each
5 - 9 = $45 each
10 - 15 = $35 each
(there are 11 price breaks in all)
-------------------
so, it would look like this

Product 1
Quantity Total
[ 4 ] $160

Product 2
[ 3 ] $195

Total $355

Many thanks in advance.

 

[Don Guillett]

This was answered by someone the first time you asked it.
Use the vlookup function. Look in HELP index for VLOOKUP

 

[Ed]

Thanks Don.
I looked at VLOOKUP as advised. I really can't extract the formula I am
looking for.
If I had just a simple example to go by that reflects my problem, I am sure
I could work through it.

This was answered by someone the first time you asked it.
Use the vlookup function. Look in HELP index for VLOOKUP

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hoping someone can help me. New to this group, so forgive me if I don't ask
the right questions.
I need to do an order form that handles 'price breaks' without having a
hundred rows of data.
For example.
Product 1
1 - 4 = $50 each
5 - 9 = $40 each
10 - 15 = $30 each
(there are 11 price breaks in all)
------------------
Product 2
1 - 4 = $65 each
5 - 9 = $45 each
10 - 15 = $35 each
(there are 11 price breaks in all)
-------------------
so, it would look like this

Product 1
Quantity Total
[ 4 ] $160

Product 2
[ 3 ] $195

Total $355

Many thanks in advance.

 

[Don Guillett]

=vlookup(1,a1:c2,2,false) will give a as the result.

1 a b
2 c d

=vlookup(a2,a1:b12,2)*a2
where a2 contains 3 and you will get $150.

Product 1


--
Don Guillett
SalesAid Software
(e-mail address removed)

Thanks Don.
I looked at VLOOKUP as advised. I really can't extract the formula I am
looking for.
If I had just a simple example to go by that reflects my problem, I am sure
I could work through it.

This was answered by someone the first time you asked it.
Use the vlookup function. Look in HELP index for VLOOKUP

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hoping someone can help me. New to this group, so forgive me if I

don't
ask

the right questions.
I need to do an order form that handles 'price breaks' without having a
hundred rows of data.
For example.
Product 1
1 - 4 = $50 each
5 - 9 = $40 each
10 - 15 = $30 each
(there are 11 price breaks in all)
------------------
Product 2
1 - 4 = $65 each
5 - 9 = $45 each
10 - 15 = $35 each
(there are 11 price breaks in all)
-------------------
so, it would look like this

Product 1
Quantity Total
[ 4 ] $160

Product 2
[ 3 ] $195

Total $355

Many thanks in advance.

 

[Mark Graesser]

Ed
Another option might be to use INDEX and MATCH. This will work well if you have the same quantity breaks for every item

Try setting up this table

1) In cells B1 through L1 enter the low end quatity of each price break
2) In cells A2 through A3 enter your first 2 items
3) Fill in the table for the pricing
4) In cell A5 enter the formula =INDEX(B2:L3,MATCH(A6,A2:A3),MATCH(A7,B1:L1)

Now you can enter the item in A6 and the qty in A7. Then A5 will return the appropriate price

The index function has the format INDEX(lookup table, Row #, Column #). The above formula finds which row contains the item, then finds which column contains the number closest to and lower then your quantity, and then returns the prices from the table

Hope this helps you out

Good Luck
Mark Graesse
(e-mail address removed)

----- Ed wrote: ----

Thanks Don
I looked at VLOOKUP as advised. I really can't extract the formula I a
looking for
If I had just a simple example to go by that reflects my problem, I am sur
I could work through it

This was answered by someone the first time you asked it
Use the vlookup function. Look in HELP index for VLOOKU

--

Don Guillet
SalesAid Softwar
(e-mail address removed)

Hoping someone can help me. New to this group, so forgive me if I don' as
the right questions
I need to do an order form that handles 'price breaks' without having
hundred rows of data
For example
Product
1 - 4 = $50 eac
5 - 9 = $40 eac
10 - 15 = $30 eac
(there are 11 price breaks in all
-----------------
Product
1 - 4 = $65 eac
5 - 9 = $45 eac
10 - 15 = $35 eac
(there are 11 price breaks in all
------------------
so, it would look like thi

Product

Quantity Tota
[ 4 ] $16

Product [ 3 ] $19
Total $35
Many thanks in advance