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