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
|
|||
|
|||
Locating values in a range and bringing up the corresponding price
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, which is on another sheet in the same file. 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. 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
|
|||
|
|||
Locating values in a range and bringing up the corresponding price
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] Not sure how you deal with weights in excess of 1,001 "Zuo" wrote: 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, which is on another sheet in the same file. 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. 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
|
|||
|
|||
Locating values in a range and bringing up the corresponding price
Duke,
Thank you for your insight. It works! Regarding the over 1000 Lbs items the rule is that IF the weight is over 1000, THEN divide the weight by 100 and multiply by the per pound $amount of the selected plan. I guess I have to nest your VLookUP formula in an IF fuction? Can you show me what you recommend? Thank you again. ZUO "Zuo" wrote: 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, which is on another sheet in the same file. 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. 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
|
|||
|
|||
Locating values in a range and bringing up the corresponding p
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. Regards, Zuo "Zuo" wrote: Duke, Thank you for your insight. It works! Regarding the over 1000 Lbs items the rule is that IF the weight is over 1000, THEN divide the weight by 100 and multiply by the per pound $amount of the selected plan. I guess I have to nest your VLookUP formula in an IF fuction? Can you show me what you recommend? Thank you again. ZUO "Zuo" wrote: 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, which is on another sheet in the same file. 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. 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 | |
|
|