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