creating a rate schedule
 

I am trying to create a rate schedule for furniture delivery in Excel 2003. I
would like Excel to automatically fill in the rate if I type in the state
delivered to and the weight of the item. See example below.


We are delivering to three separate zones 1, 2 and 3. For example Zone 1 in
cludes all the midwest states.

The rates for each Zone are determined by weight. Example: Furniture
delivered to Kansas weighing 76 to 150 lbs. is charged a rate of $173.00 and
furniture delivered to Kasas weighing 151 to 250 lbs is charged a rate of
$196.00
--
Thanks for any help you may be able to give,

Hi!

Just a quick overview of what I would do with this:

Create a table of the states delivered to and the corresponding zone.

Then create 3 tables, one for each zone with the applicable rates in
ascending order.

I would name these zone tables 1, 2 and 3.

Then when you enter* the state being delivered to, use a lookup formula to
return the corresponding zone.
*(maybe set up a drop down list for this)

Then when you enter the weight another lookup formula will lookup that zone
and weight and return the appropriate rate.

Want to see a sample file that demonstrates this? Let me know how to contact
you.

Biff

"Melissa" wrote in message
...
I am trying to create a rate schedule for furniture delivery in Excel 2003.
I
would like Excel to automatically fill in the rate if I type in the state
delivered to and the weight of the item. See example below.


We are delivering to three separate zones 1, 2 and 3. For example Zone 1
in
cludes all the midwest states.

The rates for each Zone are determined by weight. Example: Furniture
delivered to Kansas weighing 76 to 150 lbs. is charged a rate of $173.00
and
furniture delivered to Kasas weighing 151 to 250 lbs is charged a rate of
$196.00
--
Thanks for any help you may be able to give,

Hi Melissa,

I have a workbook that similar.

http://savefile.com/files/2803124

kk


"Melissa" wrote in message
...
I am trying to create a rate schedule for furniture delivery in Excel 2003.
I
would like Excel to automatically fill in the rate if I type in the state
delivered to and the weight of the item. See example below.


We are delivering to three separate zones 1, 2 and 3. For example Zone 1 in
cludes all the midwest states.

The rates for each Zone are determined by weight. Example: Furniture
delivered to Kansas weighing 76 to 150 lbs. is charged a rate of $173.00 and
furniture delivered to Kasas weighing 151 to 250 lbs is charged a rate of
$196.00
--
Thanks for any help you may be able to give,

Just another play to tinker with ..

Link to a sample file available at:
http://www.savefile.com/files/2718515

Assuming the reference table set-ups are
as per sheets: States & Rate, where

In sheet: States
(In cols A to C, zone numbers in A2:C2,
state names listed in row3 down, according
to the zones that these fall under)

----- Zones -------------
__1____2_____3
State1 State4 State7
State2 State5 State8
State3 State6 State9
etc

In sheet: Rate
(In cols A to D,
weight thresholds in A3:A6,
zone numbers in B2:D2,
zone rates in B3:D6)

______----- Zones ---
Weight__1__2__3
0_____150 175 200
76____173 200 250
151___196 220 270
251___220 250 300
etc

In another sheet: Query (say)

The state and the weight will be input in cols A and B,
from row2 down

Put in C2:
=IF(ISNUMBER(MATCH(A2,States!C:C,0)),3,IF(ISNUMBER (MATCH(A2,States!B:B,0)),2
,IF(ISNUMBER(MATCH(A2,States!A:A,0)),1,"")))

Put in D2:
=IF(OR(A2="",B2=""),"",VLOOKUP(B2,Rate!A:D,MATCH(C 2,Rate!B$2:D$2,0)+1))

Select C2:D2, copy down as far as desired

Cols C and D will return the applicable zones and the chargeable rates for
the inputs of the states and the weights in cols A & B. Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Melissa" wrote in message
...
I am trying to create a rate schedule for furniture delivery in Excel
2003. I
would like Excel to automatically fill in the rate if I type in the state
delivered to and the weight of the item. See example below.


We are delivering to three separate zones 1, 2 and 3. For example Zone 1
in
cludes all the midwest states.

The rates for each Zone are determined by weight. Example: Furniture
delivered to Kansas weighing 76 to 150 lbs. is charged a rate of $173.00
and
furniture delivered to Kasas weighing 151 to 250 lbs is charged a rate of
$196.00
--
Thanks for any help you may be able to give,