Calculations based on ranges in a VLOOKUP table

[hockeygirl33]

Hello,

I have a shipment table with costs for various weight ranges and there are more than 7 ranges, so I cannot use a NESTED IF. How do I create a formula that wil look at my shipment data and multiply it by the correct rate?

R A T E S (In Cents per Pounds)
MINIMUM WEIGHTS
Weight <500 500 1000 2000 5000 10000
COST/Lb $53.00 $0.19 $0.15 $0.14 $0.11 $0.1

So how do I write a formula to look up the correct rate for the range and multiply it by the shipment weight? For example, if the shipment weight is 900 lbs, it would fall in the 500+ weight category. I know how to use a VLOOKUP to look up specific values, but I don't know how to get it to look up the rate for data that falls within a range.

I've tried this formula with the CONCATENATE function, but to no avail. Please help!

=IF(AND(data>=500,data<1000),MAX(data*0.19,53),"") etc...

 

[Smitty]

Welcome to the Board! (What hockey team?)

You were very close! I think that HLOOKUP might work for you:

Book1
	A	B	C	D	E	F	G
1	Weight	<500	500	1000	2000	5000	10000
2	COST/Lb	$53.00	$0.19	$0.15	$0.14	$0.11	$0.10
3
4
5	100	$53.00
6	500	$0.19
7	1000	$0.15
8	2000	$0.14
9	5000	$0.11
10	10000	$0.10
Sheet1

Hope that helps,

Smitty

 

[Aladin Akyurek]

$53.00 when less than 500 and $0.19 when 500?

Care to give the result for 900 lbs?

 

[eliW]

$53.00 when less than 500 and $0.19 when 500?

Care to give the result for 900 lbs?

Aladin,
I think that what he/she ment was:

Basic rate for 1-500 Lbs. is --> $53
For every extra Lbs between 500-1000 --> $0.19

That makes the fee for 900 Lbs.

=53+400*0.19 ---> $129

And so on...

Eli

 

[Smitty]

Sorry for the confusion Aladin! (Very busy day here...)

I just put together the formula...didn't even look at the methodology behind the incremental pricing stucture.

Would have done that had the OP requested.

Smitty (who is a he EliW... )

 

[Todd Bardoni]

(who is a he EliW... )

Hockeygirl is a HE! I wonder what Freud would say about that?

 

[Aladin Akyurek]

$53.00 when less than 500 and $0.19 when 500?

Care to give the result for 900 lbs?

Aladin,
I think that what he/she ment was:

Basic rate for 1-500 Lbs. is --> $53
For every extra Lbs between 500-1000 --> $0.19

That makes the fee for 900 Lbs.

=53+400*0.19 ---> $129

And so on...

Eli

So we have...

Book4
	A	B	C	D	E	F	G
1	RATES(InCentsperPounds)
2			MINIMUMWEIGHTS
3	Weight	<500	500	1000	2000	5000	10000
4	COST/Lb	$53.00	$0.19	$0.15	$0.14	$0.11	$0.10
5
6
7	900	129
8
Sheet1

B7 houses:

=B4+IF(A7>=$C$3,(A7-$C$3)*HLOOKUP(A7,$C$3:$G$4,2,1))

 

[bosco_yip]

[quote="Aladin Akyurek]
B7 houses:

=B4+IF(A7>=$C$3,(A7-$C$3)*HLOOKUP(A7,$C$3:$G$4,2,1))[/quote]

Aladin,

Something wrong with your formula

If you enter 1006 in Cell A7

B7 returns : $128.90

The fee for 900 Lbs : $129.00 >>ok and fee for 1006 Lbs : $128.90 ???


The calculation fee for 1006 Lbs shall be :

Minimium fixed rate for below 500 Lbs. is --> $53.00

Extra fee between ">=" 500 to "<" 1000 --> $0.19 x 500 Lbs =$95.00

Extra fee between ">=" 1000 to "<" 2000 --> $0.15 x (1006-1000) Lbs =$0.90

The total fee : $148.90


Regards
Bosco

 

[logansdad]

I actually work with this stuff everyday, and I am not sure if I did this the right way, but I made it work. If you are dealing with actual freight rates, you also have to look at 950 lbs at the 500lbs rate is more expensive than paying for 1000lbs at the 1000lb rate. Customers usually get the benefeit of what ever is less expensive for them.
So I used IF satatements for Each and used the actual weight working backwards in 1 row
10M, 5M,2m 1m, 5c & L5c, then I did a n2nd row underneath and multipled each by the next weight break, and then I sed the total tht was the leastr amount. If it's too confusing, I can send you a part of the Sheet by PM > let me know if you are interested

 

[bosco_yip]

Book1
	A	B	C	D
1
2	Shipmentweight:			2,500.00lbs
3
4		Rate	Grosspayable
5	Shipmentweightchargeabletable:	$perlb	$
6
7	Onthefirstequalornotexceeding500lbs		$53.00
8	Onthenextexceeding500lbsbutnotexceedingorequalto1000lbs	0.19	$95.00
9	Onthenextexceeding1000lbsbutnotexceedingorequalto2000lbs	0.15	$150.00
10	Onthenextexceeding2000lbsbutnotexceedingorequalto5000lbs	0.14	$70.00
11	Onthenextexceeding5000lbsbutnotexceedingorequalto10000lbs	0.11
12	Onthenextexceeding10000lbs	0.10
13	TotalShipmentChargeable:			$368.00
Sheet1