Pricing scale selection

I have a sliding price scale for transport based upon wgt. The greater the
weight being transported the lower the cost per 100 lbs.
Example:
0 to 664 - flat rate of 35.00
665 to 999 - 5.25
1000 to 2499 - 4.75
2500 to 4999 - 3.00
5000 to 9999 - 2.50
10000 or greater - 1.95

How can I get Excel to automatically select the correct rate when I enter
the wgt of the shipment?

One way ..

Assume weights are in A1 down

Put in say, B1:

=VLOOKUP(A1,{0,35;665,5.25;1000,4.75;2500,3;5000,2.5;10000,1.95},2)

Copy B1 down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"emerald_dragonfly" <emeralddragonfly@discussions.microsoft.com> wrote in
message news:0BB43A34-1063-4807-BF2D-44269711BDBB@microsoft.com...
> I have a sliding price scale for transport based upon wgt. The greater
the
> weight being transported the lower the cost per 100 lbs.
> Example:
> 0 to 664 - flat rate of 35.00
> 665 to 999 - 5.25
> 1000 to 2499 - 4.75
> 2500 to 4999 - 3.00
> 5000 to 9999 - 2.50
> 10000 or greater - 1.95
>
> How can I get Excel to automatically select the correct rate when I enter
> the wgt of the shipment?

Hi, ed. Try this: http://www.myexpertsonline.com/freedls/shipwts.zip
Note that I provide a formula to get the rate AND a formula to get the
amount to charge for shipping.
rate: =IF(C7<665,35,VLOOKUP(C7,Weights!A2:B6,2))
amount: =IF(C8<665,35,VLOOKUP(C8,Weights!A2:B6,2)*C8/100)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"emerald_dragonfly" <emeralddragonfly@discussions.microsoft.com> wrote in
message news:0BB43A34-1063-4807-BF2D-44269711BDBB@microsoft.com...
> I have a sliding price scale for transport based upon wgt. The greater
the
> weight being transported the lower the cost per 100 lbs.
> Example:
> 0 to 664 - flat rate of 35.00
> 665 to 999 - 5.25
> 1000 to 2499 - 4.75
> 2500 to 4999 - 3.00
> 5000 to 9999 - 2.50
> 10000 or greater - 1.95
>
> How can I get Excel to automatically select the correct rate when I enter
> the wgt of the shipment?

=IF(A1<665,35,LOOKUP(A1,{665,1000,2500,5000,10000;5.25,4.75,3,2.5,1.95})

Vaya con Dios,
Chuck, CABGx3


"emerald_dragonfly" <emeralddragonfly@discussions.microsoft.com> wrote in
message news:0BB43A34-1063-4807-BF2D-44269711BDBB@microsoft.com...
> I have a sliding price scale for transport based upon wgt. The greater
the
> weight being transported the lower the cost per 100 lbs.
> Example:
> 0 to 664 - flat rate of 35.00
> 665 to 999 - 5.25
> 1000 to 2499 - 4.75
> 2500 to 4999 - 3.00
> 5000 to 9999 - 2.50
> 10000 or greater - 1.95
>
> How can I get Excel to automatically select the correct rate when I enter
> the wgt of the shipment?

I am having trouble adding a maximum total price to this formula. I want the
calculated total to never exceed 465.00 How can I do that?

"emerald_dragonfly" wrote:

> I have a sliding price scale for transport based upon wgt. The greater the
> weight being transported the lower the cost per 100 lbs.
> Example:
> 0 to 664 - flat rate of 35.00
> 665 to 999 - 5.25
> 1000 to 2499 - 4.75
> 2500 to 4999 - 3.00
> 5000 to 9999 - 2.50
> 10000 or greater - 1.95
>
> How can I get Excel to automatically select the correct rate when I enter
> the wgt of the shipment?

=MIN(YourSumFormula,465)

Vaya con Dios,
Chuck, CABGx3


"emerald_dragonfly" <emeralddragonfly@discussions.microsoft.com> wrote in
message news:059E08E5-4B7C-49B2-9D2C-0A0FC63AA75A@microsoft.com...
> I am having trouble adding a maximum total price to this formula. I want
the
> calculated total to never exceed 465.00 How can I do that?
>
> "emerald_dragonfly" wrote:
>
> > I have a sliding price scale for transport based upon wgt. The greater
the
> > weight being transported the lower the cost per 100 lbs.
> > Example:
> > 0 to 664 - flat rate of 35.00
> > 665 to 999 - 5.25
> > 1000 to 2499 - 4.75
> > 2500 to 4999 - 3.00
> > 5000 to 9999 - 2.50
> > 10000 or greater - 1.95
> >
> > How can I get Excel to automatically select the correct rate when I
enter
> > the wgt of the shipment?