If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Finding the corresponding value in a range?
Hi,
I appreciate if somebody can help me with a formula. In one spread sheet I have a list of products and their weights. In that same sheet I want to create a formula that brings up the corresponding applicable freight $ based on the range in which the weight value falls in the table below. i.e. if the weight is 129 lbs then it should bring up a freight cost of $227 if I am using the Bronze plan. Additionally IF the weight is over 1000, THEN divide the weight by 100 and multiply by the per pound $amount of the selected plan. Weights and Corresponding Freight rates Weight (lbs.) Bronze Silver Gold 0-100 $160 $175 $204 101-200 $227 $247 $261 201-300 $268 $282 $303 301-400 $300 $327 $352 401-500 $363 $409 $435 501-600 $413 $471 $496 601-700 $479 $548 $574 701-800 $537 $624 $653 801-900 $589 $713 $742 901-1,000 $642 $780 $839 1,001 lbs & over (cwt) $62 $77 $82 Kind Regards, Zuo |
#2
|
|||
|
|||
Finding the corresponding value in a range?
One way to get it all ...
http://cjoint.com/?czazitAMok Freight charge table.xls Inspiring? hit YES below -- Max Singapore --- "Zuo" wrote: I appreciate if somebody can help me with a formula. In one spread sheet I have a list of products and their weights. In that same sheet I want to create a formula that brings up the corresponding applicable freight $ based on the range in which the weight value falls in the table below. i.e. if the weight is 129 lbs then it should bring up a freight cost of $227 if I am using the Bronze plan. Additionally IF the weight is over 1000, THEN divide the weight by 100 and multiply by the per pound $amount of the selected plan. Weights and Corresponding Freight rates Weight (lbs.) Bronze Silver Gold 0-100 $160 $175 $204 101-200 $227 $247 $261 201-300 $268 $282 $303 301-400 $300 $327 $352 401-500 $363 $409 $435 501-600 $413 $471 $496 601-700 $479 $548 $574 701-800 $537 $624 $653 801-900 $589 $713 $742 901-1,000 $642 $780 $839 1,001 lbs & over (cwt) $62 $77 $82 Kind Regards, Zuo |
#3
|
|||
|
|||
Finding the corresponding value in a range?
Max,
Thanks for your very detailed answer, very professional! I have a question though, Duke had proposed the following solution which seems simpler but had a problem which I could not resolve, see below. Is this a better solution for the first part of the table? If so, how do I resolve the problem with his formula? “Duke” wrote: First, change your first column so that it shows the bottom end of the weight range, and your table then looks like this Weight Bronze Silver Gold 0 $160 $175 $204 101 $227 $247 $261 201 $268 $282 $303 301 $300 $327 $352 401 $363 $409 $435 501 $413 $471 $496 601 $479 $548 $574 701 $537 $624 $653 801 $589 $713 $742 901 $642 $780 $839 Assuming the column headings are in row 3, columns B:E and the table is in B4:E13, then use the formula =VLOOKUP([weight],B4:E13,LOOKUP([plan],{"Bronze","Silver","Gold"},{2,3,4})) replace [weight] with the cell address where you enter the weight, and likewise for [plan] “Zuo” wrote: Duke I just noticed that the formula works well with the Bronze and the Silver plans, however when I input Gold as a plan it returns the values of the Bronze plan. Please advise. "Max" wrote: One way to get it all ... http://cjoint.com/?czazitAMok Freight charge table.xls Inspiring? hit YES below -- Max Singapore --- "Zuo" wrote: I appreciate if somebody can help me with a formula. In one spread sheet I have a list of products and their weights. In that same sheet I want to create a formula that brings up the corresponding applicable freight $ based on the range in which the weight value falls in the table below. i.e. if the weight is 129 lbs then it should bring up a freight cost of $227 if I am using the Bronze plan. Additionally IF the weight is over 1000, THEN divide the weight by 100 and multiply by the per pound $amount of the selected plan. Weights and Corresponding Freight rates Weight (lbs.) Bronze Silver Gold 0-100 $160 $175 $204 101-200 $227 $247 $261 201-300 $268 $282 $303 301-400 $300 $327 $352 401-500 $363 $409 $435 501-600 $413 $471 $496 601-700 $479 $548 $574 701-800 $537 $624 $653 801-900 $589 $713 $742 901-1,000 $642 $780 $839 1,001 lbs & over (cwt) $62 $77 $82 Kind Regards, Zuo |
#4
|
|||
|
|||
Finding the corresponding value in a range?
Zuo, ask your new query in a fresh thread if Duke did not respond further in
that thread. Here, does my solution address ALL of your specs which you asked in your original posting here? If so, take a moment, close it off, hit the YES in that earlier response. Thanks. -- Max Singapore --- "Zuo" wrote: Max, Thanks for your very detailed answer, very professional! I have a question though, Duke had proposed the following solution which seems simpler but had a problem which I could not resolve, see below. Is this a better solution for the first part of the table? If so, how do I resolve the problem with his formula? |
#5
|
|||
|
|||
Finding the corresponding value in a range?
Lookup requires that values be in ascending order. So you need:
=VLOOKUP([weight],B4:E13,LOOKUP([plan],{"Bronze","Gold","Silver"},{2,4,3})) Regards, Fred "Zuo" wrote in message ... Max, Thanks for your very detailed answer, very professional! I have a question though, Duke had proposed the following solution which seems simpler but had a problem which I could not resolve, see below. Is this a better solution for the first part of the table? If so, how do I resolve the problem with his formula? “Duke” wrote: First, change your first column so that it shows the bottom end of the weight range, and your table then looks like this Weight Bronze Silver Gold 0 $160 $175 $204 101 $227 $247 $261 201 $268 $282 $303 301 $300 $327 $352 401 $363 $409 $435 501 $413 $471 $496 601 $479 $548 $574 701 $537 $624 $653 801 $589 $713 $742 901 $642 $780 $839 Assuming the column headings are in row 3, columns B:E and the table is in B4:E13, then use the formula =VLOOKUP([weight],B4:E13,LOOKUP([plan],{"Bronze","Silver","Gold"},{2,3,4})) replace [weight] with the cell address where you enter the weight, and likewise for [plan] “Zuo” wrote: Duke I just noticed that the formula works well with the Bronze and the Silver plans, however when I input Gold as a plan it returns the values of the Bronze plan. Please advise. "Max" wrote: One way to get it all ... http://cjoint.com/?czazitAMok Freight charge table.xls Inspiring? hit YES below -- Max Singapore --- "Zuo" wrote: I appreciate if somebody can help me with a formula. In one spread sheet I have a list of products and their weights. In that same sheet I want to create a formula that brings up the corresponding applicable freight $ based on the range in which the weight value falls in the table below. i.e. if the weight is 129 lbs then it should bring up a freight cost of $227 if I am using the Bronze plan. Additionally IF the weight is over 1000, THEN divide the weight by 100 and multiply by the per pound $amount of the selected plan. Weights and Corresponding Freight rates Weight (lbs.) Bronze Silver Gold 0-100 $160 $175 $204 101-200 $227 $247 $261 201-300 $268 $282 $303 301-400 $300 $327 $352 401-500 $363 $409 $435 501-600 $413 $471 $496 601-700 $479 $548 $574 701-800 $537 $624 $653 801-900 $589 $713 $742 901-1,000 $642 $780 $839 1,001 lbs & over (cwt) $62 $77 $82 Kind Regards, Zuo |
Thread Tools | |
Display Modes | |
|
|