R
Robert_Steel
I am looking for some ideas about simplifying a formula that calculates
the weight tolerance (TNE) according to the following rules
Nominal quantity (Qn) Tolerable negative error (TNE)
g or ml as % of Qn g or ml
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
it is a variation on the variable rate topic that is often discussed.
example calculations
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)
Note for the number at the boundary of each range it does not matter which
calculation is used as they return the same amount.
500*3% = 15 (the same as the fixed amount in the 500 to 1000 range)
The formula I currently use relies on the table above for a lookup
=N(VLOOKUP(B19,A5:F13,5,1))*B19/100+N(VLOOKUP(B19,A5:F13,6,1))
I have removed the ROUNDUP simply to ease legibility.
This formula feels clunky and I would like to simplify it and remove the
need for a lookup table.
Any ideas welcomed
Cheers RES
the weight tolerance (TNE) according to the following rules
Nominal quantity (Qn) Tolerable negative error (TNE)
g or ml as % of Qn g or ml
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
it is a variation on the variable rate topic that is often discussed.
example calculations
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)
Note for the number at the boundary of each range it does not matter which
calculation is used as they return the same amount.
500*3% = 15 (the same as the fixed amount in the 500 to 1000 range)
The formula I currently use relies on the table above for a lookup
=N(VLOOKUP(B19,A5:F13,5,1))*B19/100+N(VLOOKUP(B19,A5:F13,6,1))
I have removed the ROUNDUP simply to ease legibility.
This formula feels clunky and I would like to simplify it and remove the
need for a lookup table.
Any ideas welcomed
Cheers RES