Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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, |
#2
|
|||
|
|||
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, |
#3
|
|||
|
|||
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, |
#4
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a work schedule | Excel Discussion (Misc queries) | |||
subtraction of times, convert & multiply by a conditioned rate | Excel Worksheet Functions | |||
Is there an adjustable rate amortization schedule in Excel? | Excel Worksheet Functions | |||
APR - Annual Percentage Rate to Actual Interest Rate | Excel Worksheet Functions | |||
Creating an Amortization schedule ? | Excel Discussion (Misc queries) |