# Calculation based on variable labels and variable ranges

#### brakiachi

##### New Member
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

 Analyte Declared Content Tolerance Below Tolerance Above Fat <16 2 4 Fat 16-24 12.5% 25% Fat >24 3 6 Moisture <2 no limit 0.4 Moisture 2-5 no limit 20% Moisture 5-12.5 no limit 1 Moisture >12.5 no limit 8% Ash <8 2 1 Ash 8-32 25% 12.5% Ash >32 8 4

<tbody>
</tbody>

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?

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### DRSteele

##### Well-known Member
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
ABCDEFGH
2AnalyteDeclared Content LowDeclared Content HighTolerance BelowType_BelowTolerance AboveType_Above
3Fat0.016.02.00value4.00value
4Fat16.024.012.50%rate25.00%rate
5Fat24.03.00value6.00value
6Moisture0.02.00value0.40value
7Moisture2.05.00value20.00%rate
8Moisture5.012.50value1.00value
9Moisture12.50value8%rate
10Ash0.08.02.00value1.00value
11Ash8.032.025.00%rate12.50%rate
12Ash32.08.00value4.00value
13
14AnalyteDeclared ContentTolerance BelowTolerance Above
15Fat8.0006.00012.000
Sheet48
Cell Formulas
RangeFormula
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]

Last edited:

#### StephenCrump

##### MrExcel MVP
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
ABCDEF
1AnalyteDeclaredTolerance -Tolerance +
2Fat020%40%
3Fat16012.50%025.00%
4Fat2430%60%
5Moisture000%0.40%
6Moisture200%020%
7Moisture500%10%
8Moisture12.500%08%
9Ash020%10%
10Ash8025%012.5%
11Ash3280%40%
12
13DeclaredTolerance -Tolerance +
14Fat8612
15Ash20522.5
Sheet1

#### StephenCrump

##### MrExcel MVP
... 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
ABCDEFGHIJK
1AnalyteDeclaredTolerance -Tolerance +AnalyteDeclaredTolerance -Tolerance +
2Fat020%40%Fat<1624
3Fat16212.50%425%Fat16-2412.50%25%
4Fat2430%60%Fat>2436
5Moisture000%0.40%Moisture<2no limit0.4
6Moisture200%0.420%Moisture2-5no limit20%
7Moisture500%110%Moisture5-12.5no limit10%
8Moisture12.500%1.758%Moisture>12.5no limit8%
9Ash020%10%Ash<821
10Ash8225%112.5%Ash8-3225%12.50%
11Ash3280%40%Ash>3284
12
13DeclaredTolerance -Tolerance +
14Moisture111.4
15Moisture444.8
16Moisture667.1
17Moisture101011.5
18Moisture12.512.514.25
19Moisture202022.35
Sheet1

Replies
3
Views
92
Replies
1
Views
254
Replies
14
Views
137
Replies
2
Views
261
Replies
5
Views
394

1,172,124
Messages
5,879,202
Members
433,409
Latest member
momo2809

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back