a few condition

  • Thread starter Thread starter teratak
  • Start date Start date
T

teratak

hi,

I am using excel 2003. I am trying to formulate a transportation cost base
on weight with a few condition.

If the total weight is equal or less than 500g the cost is $5
If the total weight is equal or less than 750g the cost is $6
If the total weight is equal or less than 1000g the cost is $8
If the total weight is equal or less than 1250g the cost is $9
If the total weight is equal or less than 1500g the cost is $10
If the total weight is equal or less than 1750g the cost is $11
If the total weight is equal or less than 2000g the cost is $12

How do I set the formula?
 
Is the cost $5 from 500 to 749g?
If so, set up a table of weights and costs, say in A1:B7. Name this range
Cost.
Where you want to retrieve the ifo, insert the formula
=VLOOKUP(<ref to cell with actual weight>,Cost,2,1)
eg =VLOOKUP(G11,Cost,2,1)

and it will give you the cost.
 
hi,

I am using excel 2003. I am trying to formulate a transportation cost base
on weight with a few condition.

If the total weight is equal or less than 500g the cost is $5
If the total weight is equal or less than 750g the cost is $6
If the total weight is equal or less than 1000g the cost is $8
If the total weight is equal or less than 1250g the cost is $9
If the total weight is equal or less than 1500g the cost is $10
If the total weight is equal or less than 1750g the cost is $11
If the total weight is equal or less than 2000g the cost is $12

How do I set the formula?
Click to expand...


I think this is a two parter.

The first is to get the lookup table set up with the values needed.
So, out of sight (say to the right) put in the table of data - in this
case something like

0 5
501 6
751 8
1001 9
1251 10
1501 11
1751 12

Select this area, and then go to the name box and type in a name eg
WeightTable.

Then use a vlookup statement

If you are using row 1 for the titles, then in A2 you might have the
weight you want to look up

In B2 you would put the formula

=VLOOKUP(A2,WeightTable,2)

Vlookup looks summat like...

=vlookup(The value you want to look up, the range where the
lookuptable is, the column in the table to return)

Data in vlookup tables must be sorted ascending (BICBW!)

HTH

Noz
 
Assuming your values are always whole numbers...

=CHOOSE(1+INT((D1-1)/250),5,5,6,8,9,10,11,12)

and format the cell as Currency with zero decimal places. If you will be
copying this formula down, then you might want to modify it to handle blank
cells

=IF(D1="","",CHOOSE(1+INT((D1-1)/250),5,5,6,8,9,10,11,12))

Rick
 
Hi,
Is the jump from $6 to $8 intentional?
You could use this formula in the cell where you want the result.
=VLOOKUP(A1,{1,5;501,6;751,8;1001,9;1251,10;1501,11;1751,12;2001,"??"},2)
It's a bit cumbersome, but you don't need a separate table.
You need to enter the actual weight into A1. Change the A1 in the formula to
whatever cell you want to enter the weight.
Regards - Dave.
 
Or, taking a cue from Teethless mama's post, these slightly simpler
formulas...

=CHOOSE(CEILING(A1/250,1),5,5,6,8,9,10,11,12)

or

=IF(A1="","",CHOOSE(CEILING(A1/250,1),5,5,6,8,9,10,11,12))

and, using these, your values no longer need to be integers.

Rick
 
=HLOOKUP(A2,{0,500.5,750.5,1000.5,1250.5,1500.5,1750.5,2000.5;5,6,8,9,10,11,12,"Invalid"},2)

For weight in cell A2 - You didn't say if you were working with only whole
numbers or what you wanted if the weight exceeded 2000. This formula will
treat 750.4 as less than or equal to 750 and 750.5 as greater. If a weight
exceeds 2000 - the cell will display the text Invalid.
 
Back
Top