Calculation based on variable labels and variable ranges

brakiachi

New Member
Joined
Jan 7, 2016
Messages
20
Office Version
  1. 2010
Platform
  1. Windows
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

AnalyteDeclared Content Tolerance BelowTolerance Above
Fat<1624
Fat16-2412.5%25%
Fat>2436
Moisture<2no limit0.4
Moisture2-5no limit20%
Moisture5-12.5no limit1
Moisture>12.5no limit8%
Ash<821
Ash8-3225%12.5%
Ash>3284

<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
Joined
Mar 31, 2015
Messages
2,504
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 18, 2013
Messages
4,922
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 18, 2013
Messages
4,922
Office Version
  1. 365
Platform
  1. Windows
... 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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top