Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
breakpoint calculation
On Tue, 21 Oct 2008 17:32:02 -0700 (PDT), Oliver
wrote: The following is a presentation of rate table: <45kg +45KG +100KG +300KG +500KG +10000KG $9.5 $8.5 $7.5 $4.5 $3.5 $1.5 It reads if weight is smaller than 45kg then we charge $9.5 per kg; if weight is between 45 and 100kg then we charge $8.5 and so on; is there an easy way to write a formula to calculate the total charge based on the weight? I could use if(weight<45,9.5*weight,if(and(weight45,weight<10 0), 8.5*weight, etc etc) but it will be a long formula... i think it must have an easy way to do this... If your Rate Table is in a horizontal alignment, as you show, then HLOOKUP will do the job. Set up the table someplace as follows: 0 45 100 300 500 10000 9.5 8.5 7.5 4.5 3.5 1.5 NAME it RateTable. Then, with your weight in A1, use this formula: =HLOOKUP(A1,RateTable,2)*A1 --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
breakpoint calculation | Excel Worksheet Functions | |||
Once only calculation - help please | Excel Discussion (Misc queries) | |||
Breakpoint will not stop Excel subroutine | Excel Discussion (Misc queries) | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions |