Calculation based on variable labels and variable ranges

[brakiachi]

I'm having a hard time coming up with an elegant solution for this. I have a table that explains legal tolerances for nutrition based on certain analytes and their values

Table look like this, but has many more values in it

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Analyte[/TD]
[TD]Declared Content [/TD]
[TD]Tolerance Below[/TD]
[TD]Tolerance Above[/TD]
[/TR]
[TR]
[TD]Fat[/TD]
[TD]<16[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Fat[/TD]
[TD]16-24[/TD]
[TD]12.5%[/TD]
[TD]25%[/TD]
[/TR]
[TR]
[TD]Fat[/TD]
[TD]>24[/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Moisture[/TD]
[TD]<2[/TD]
[TD]no limit[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]Moisture[/TD]
[TD]2-5[/TD]
[TD]no limit[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]Moisture[/TD]
[TD]5-12.5[/TD]
[TD]no limit[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Moisture[/TD]
[TD]>12.5[/TD]
[TD]no limit[/TD]
[TD]8%[/TD]
[/TR]
[TR]
[TD]Ash[/TD]
[TD]<8[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Ash[/TD]
[TD]8-32[/TD]
[TD]25%[/TD]
[TD]12.5%[/TD]
[/TR]
[TR]
[TD]Ash[/TD]
[TD]>32[/TD]
[TD]8[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Just to give an example:
If the declared content of fat is 8, the tolerance below is 8-2=6, and above is 8+4=12
If the declared content of fat is 20, the tolerance below is 20-20*12.5%=17.5, and the tolerance above is 20+20*25%=25

So on the input side we will have a table that has the analytes and the declared values, and the output side will have the tolerances.
The input side can also have the analytes arranged in any order, not necessarily the same order.

The only solution I have is creating a table to arrange the analytes in a specific order and then having to to put in if statements based on specific analytes and ranges
eg. for fat putting in: =if(A2<16,A2-B2,if(A2>24,A2-3,A2-A2*0.125))

Is there an elegant solution where I won't have to change each formula should the tolerances change? That is, I can just update the table, and not have to update each formula when the table changes?

 

[DRSteele]

The problem here is that there are two lookup values and they are used in three lookups. I thought it best to use IF statements and mutiply arrays; this way, the data can be sorted in any manner and the formulas will still work. The solution I have here is hardly elegant and it reqiuires you to alter the table of data - for the upper bounds, just leave them blank. I assume that "no limit" on the lower bound means zero.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H
2	Analyte	Declared Content Low	Declared Content High	Tolerance Below	Type_Below	Tolerance Above	Type_Above
3	Fat	0.0	16.0	2.00	value	4.00	value
4	Fat	16.0	24.0	12.50%	rate	25.00%	rate
5	Fat	24.0		3.00	value	6.00	value
6	Moisture	0.0	2.0	0	value	0.40	value
7	Moisture	2.0	5.0	0	value	20.00%	rate
8	Moisture	5.0	12.5	0	value	1.00	value
9	Moisture	12.5		0	value	8%	rate
10	Ash	0.0	8.0	2.00	value	1.00	value
11	Ash	8.0	32.0	25.00%	rate	12.50%	rate
12	Ash	32.0		8.00	value	4.00	value
13
14	Analyte	Declared Content	Tolerance Below	Tolerance Above
15	Fat	8.000	6.000	12.000
Sheet48

Cell Formulas
Range	Formula
C15	=IF(MIN(IF(IF(IF((A15=$A$3:$A$12)*(B15>=$B$3:$B$12)*(B15ISBLANK($C$3:$C$12),9.99999999999999E+307,$C$3:$C$12)),$E$3:$E$12)="rate",1,0),((1-IF((A15=$A$3:$A$12)*(B15>=$B$3:$B$12)*(B15ISBLANK($C$3:$C$12),9.99999999999999E+307,$C$3:$C$12)),$D$3:$D$12))*B15),B15-IF((A15=$A$3:$A$12)*(B15>=$B$3:$B$12)*(B15ISBLANK($C$3:$C$12),9.99999999999999E+307,$C$3:$C$12)),$D$3:$D$12)))=B15,0,MIN(IF(IF(IF((A15=$A$3:$A$12)*(B15>=$B$3:$B$12)*(B15ISBLANK($C$3:$C$12),9.99999999999999E+307,$C$3:$C$12)),$E$3:$E$12)="rate",1,0),((1-IF((A15=$A$3:$A$12)*(B15>=$B$3:$B$12)*(B15ISBLANK($C$3:$C$12),9.99999999999999E+307,$C$3:$C$12)),$D$3:$D$12))*B15),B15-IF((A15=$A$3:$A$12)*(B15>=$B$3:$B$12)*(B15ISBLANK($C$3:$C$12),9.99999999999999E+307,$C$3:$C$12)),$D$3:$D$12))))
D15	=MAX(IF(IF(IF((A15=$A$3:$A$12)*(B15>=$B$3:$B$12)*(B15ISBLANK($C$3:$C$12),9.99999999999999E+307,$C$3:$C$12)),$G$3:$G$12)="rate",1,0),((1+IF((A15=$A$3:$A$12)*(B15>=$B$3:$B$12)*(B15ISBLANK($C$3:$C$12),9.99999999999999E+307,$C$3:$C$12)),$F$3:$F$12))*B15),B15+IF((A15=$A$3:$A$12)*(B15>=$B$3:$B$12)*(B15ISBLANK($C$3:$C$12),9.99999999999999E+307,$C$3:$C$12)),$F$3:$F$12)))

[/FONT]

 

[StephenCrump]

C14: =B14-SUMPRODUCT(--(ROW(A$2:A$11)=MATCH(B14,IF(A14=A$2:A$11,B$2:B$11),1)+ROW(A$2)-1),C$2:C$11+D$2:D$11*B14)
D14: =B14+SUMPRODUCT(--(ROW(A$2:A$11)=MATCH(B14,IF(A14=A$2:A$11,B$2:B$11),1)+ROW(A$2)-1),E$2:E$11+F$2:F$11*B14)
(array-entered)

Book1
	A	B	C	D	E	F
1	Analyte	Declared	Tolerance -		Tolerance +
2	Fat	0	2	0%	4	0%
3	Fat	16	0	12.50%	0	25.00%
4	Fat	24	3	0%	6	0%
5	Moisture	0	0	0%	0.4	0%
6	Moisture	2	0	0%	0	20%
7	Moisture	5	0	0%	1	0%
8	Moisture	12.5	0	0%	0	8%
9	Ash	0	2	0%	1	0%
10	Ash	8	0	25%	0	12.5%
11	Ash	32	8	0%	4	0%
12
13		Declared	Tolerance -	Tolerance +
14	Fat	8	6	12
15	Ash	20	5	22.5
Sheet1

 

[StephenCrump]

... but maybe your table can look like this (one change highlighted).

In which case, more robustly:

C14: =B14-SUMPRODUCT(--(ROW(A$2:A$11)=MATCH(B14,IF(A14=A$2:A$11,B$2:B$11),1)+ROW(A$2)-1),C$2:C$11+D$2:D$11*(B14-B$2:B$11))
D14: =B14+SUMPRODUCT(--(ROW(A$2:A$11)=MATCH(B14,IF(A14=A$2:A$11,B$2:B$11),1)+ROW(A$2)-1),E$2:E$11+F$2:F$11*(B14-B$2:B$11))

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Analyte	Declared	Tolerance -		Tolerance +			Analyte	Declared	Tolerance -	Tolerance +
2	Fat	0	2	0%	4	0%		Fat	<16	2	4
3	Fat	16	2	12.50%	4	25%		Fat	16-24	12.50%	25%
4	Fat	24	3	0%	6	0%		Fat	>24	3	6
5	Moisture	0	0	0%	0.4	0%		Moisture	<2	no limit	0.4
6	Moisture	2	0	0%	0.4	20%		Moisture	2-5	no limit	20%
7	Moisture	5	0	0%	1	10%		Moisture	5-12.5	no limit	10%
8	Moisture	12.5	0	0%	1.75	8%		Moisture	>12.5	no limit	8%
9	Ash	0	2	0%	1	0%		Ash	<8	2	1
10	Ash	8	2	25%	1	12.5%		Ash	8-32	25%	12.50%
11	Ash	32	8	0%	4	0%		Ash	>32	8	4
12
13		Declared	Tolerance -	Tolerance +
14	Moisture	1	1	1.4
15	Moisture	4	4	4.8
16	Moisture	6	6	7.1
17	Moisture	10	10	11.5
18	Moisture	12.5	12.5	14.25
19	Moisture	20	20	22.35
Sheet1