Excel Formula Postage Prices

  • Thread starter Thread starter Sizz
  • Start date Start date
S

Sizz

Hello people,

Im trying to create a formula using excel. For Weight & Postage Prices

I want it to say:
If the weight is less than 1kg, the price is X
If the weight is between 1.1kg and 20kg, the price is X
If the weight is between 20.1kg and 110kg, the price is X
If the weight is between 110.5kg to 500kg, the price is X


I have 4 shipping band/prices, for eg,
band 1 is £10,
band 2 is £20,
band 3 is £30,
band 4 is £40.

So, if the weight of the parcel falls in a certain band, the price is
automatically calculated.

This all needs to be in 1 cell.

You help is much appreciated.

Thanks
 
Hello people,

Im trying to create a formula using excel. For Weight & Postage Prices

I want it to say:
If the weight is less than 1kg, the price is X
If the weight is between 1.1kg and 20kg, the price is X
If the weight is between 20.1kg and 110kg, the price is X
If the weight is between 110.5kg to 500kg, the price is X


I have 4 shipping band/prices, for eg,
band 1 is £10,
band 2 is £20,
band 3 is £30,
band 4 is £40.

So, if the weight of the parcel falls in a certain band, the price is
automatically calculated.

This all needs to be in 1 cell.

You help is much appreciated.

Thanks
Click to expand...

You can use the VLOOKUP function.

Your request is somewhat unclear, so I made a few assumptions.

With your weight in A1:

=VLOOKUP(A1,{0,10;1,20;20,30;110,40;500,"Undetermined"},2)

You also indicated that you wanted everything in a single cell. The data would
be simpler to maintain if you put the array constant (the portion between the
braces) into an array of cells, but you can always change that.

The lack of clarity is because there are certain possible weights which are
undefined in your description
e.g.: Wt 1.0-1.1 kg
Wt 20.0-20.1 kg
Wt 110-110.5 kg
Wt > 500 kg

So you'll have to see if the assumptions I made about these undefined areas
are OK; or else change the values in the lookup table.
--ron
 
Try this:

=LOOKUP(A1,{0,0.01,1.1,20.1,110.5;0,10,20,30,40})
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Hello people,

Im trying to create a formula using excel. For Weight & Postage Prices

I want it to say:
If the weight is less than 1kg, the price is X
If the weight is between 1.1kg and 20kg, the price is X
If the weight is between 20.1kg and 110kg, the price is X
If the weight is between 110.5kg to 500kg, the price is X


I have 4 shipping band/prices, for eg,
band 1 is £10,
band 2 is £20,
band 3 is £30,
band 4 is £40.

So, if the weight of the parcel falls in a certain band, the price is
automatically calculated.

This all needs to be in 1 cell.

You help is much appreciated.

Thanks
 
Try this:

=LOOKUP(A1,{0,0.01,1.1,20.1,110.5;0,10,20,30,40})
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


Hello people,

Im trying to create a formula using excel. For Weight & Postage Prices

I want it to say:
If the weight is less than 1kg, the price is X
If the weight is between 1.1kg and 20kg, the price is X
If the weight is between 20.1kg and 110kg, the price is X
If the weight is between 110.5kg to 500kg, the price is X

I have 4 shipping band/prices, for eg,
band 1 is £10,
band 2 is £20,
band 3 is £30,
band 4 is £40.

So, if the weight of the parcel falls in a certain band, the price is
automatically calculated.

This all needs to be in 1 cell.

You help is much appreciated.

Thanks
Click to expand...

Thanks Guys,

Works!

Much appreciated!
 
Back
Top