Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
a few condition
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
a few condition
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. -- Hth Kassie Kasselman Change xxx to hotmail "teratak" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
a few condition
In article ,
teratak said... 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? 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
a few condition
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 "teratak" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
a few condition
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,1 1;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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
a few condition
Try this:
=MAX(CEILING(A1/250,1)+(A1750)+3,5) "teratak" wrote: 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
a few condition
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 "Rick Rothstein (MVP - VB)" wrote in message ... 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 "teratak" wrote in message ... 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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
a few condition
=HLOOKUP(A2,{0,500.5,750.5,1000.5,1250.5,1500.5,17 50.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. "teratak" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if condition | Excel Discussion (Misc queries) | |||
lookup with multiple condition, but one condition to satisfy is en | Excel Worksheet Functions | |||
Combine an OR condition with an AND condition | Excel Discussion (Misc queries) | |||
Condition 1 overules condition 2? | Excel Worksheet Functions | |||
Condition IF | Excel Discussion (Misc queries) |