Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Twist on the variable rate problem
I posted about this problem before Christmas but got no response.
I have rephrased it and hope this does better... The standard variable rate problem has a variable proportional rate dependent on the input. My problem is different because I need an alternating proportional rate then flat amount. I have a formula that does the job but is long-winded and complex. I am hoping someone will spot a more simple way to do it. The function is needed to calculate the permitted weight or volume tolerance. The table used is Nominal quantity (Qn) Tolerable negative error (TNE) g or ml as % of Qn g or ml (fixed) 5 to 50 9 - 50 to 100 - 4.5 100 to 200 4.5 - 200 to 300 - 9 300 to 500 3 - 500 to 1000 - 15 1000 to 10000 1.5 - 10000 to 15000 - 150 15000 and over 1 - TNEs shown as percentage should be rounded up to the nearest 1/10 g or ml above eg. Qn = 20 then TNE = 20*9% = 1.8 Qn = 60 then TNE = fixed 4.5 Qn = 95 then TNE = fixed 4.5 Qn = 105 then TNE = 105*4.5% = 4.8 (rounded up) I have declared a named variable Std_Data = {5,0.09,0;50,0,4.5;100,0.045,0;200,0,9;300,0.03,0; 500,0,15;1000,0.015,0;10000,0,150;15000,0.01,0} and used the formula =ROUNDUP(SUMPRODUCT(VLOOKUP(A1,Std_Data,{2,3}),A1* {1,0}+{0,1}),1) I hope this makes sense and gets a response, if only to explain how I should have structured the question. many thanks RES |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
variable height variable width stacked bar charts | Charts and Charting in Excel | |||
how do I calculate a monthly payment based on a variable rate? | Excel Worksheet Functions | |||
Multi-lookups on variable data | Excel Worksheet Functions | |||
APR - Annual Percentage Rate to Actual Interest Rate | Excel Worksheet Functions | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) |