I have a table that I am doing as follows:
Index Diff Exc Sym Sec Result Criteria Met
3.42 -2.07 0.86 0.92 1.83 1.95
-0.38 -2.93 0.87 0.23 -2.01 0.09
-2.67 -1.84 0.87 -2.49 -3.48 1.32
-0.65 -0.98 0.46 0.98 -2.01 0.00
-0.73 -2.79 -1.07 -2.15 -1.44 -0.10
0.15 2.33 -0.46 -0.66 3.17 0.38 0.38
0.90 -3.68 -0.72 -1.01 -1.36 1.69
0.68 -1.12 -0.36 0.73 -1.34 -0.29
-1.19 -1.70 -0.56 -1.31 1.45 0.49
-0.45 -0.69 -0.56 -1.22 0.00 -0.49
2.94 8.38 2.21 6.25 4.96 1.74
-1.04 7.36 2.59 3.00 2.17 2.97
1.21 1.73 3.05 1.48 3.56 0.77
-1.10 1.86 0.60 1.18 1.07 -0.49
-0.89 -3.19 -1.78 -2.24 -4.26 -0.81
-1.17 -3.44 0.11 -1.22 3.66 0.36
0.52 0.92 -1.02 0.38 1.96 -1.40 -1.40
-0.90 3.01 -0.86 0.62 0.97 -0.50 -0.50
2.78 1.46 0.00 0.47 1.95 0.84
Max Min
Index 2.00 -2.00
Diff 10.00 0.00
Exc 0.00 -10.00
Sym 10.00 -10.00
Sec 20.00 0.00
The headers Index, Diff, Exc, Sym, Sec, Result all the data. There is a formula in the metrics criteria column that checks whether previous headings fall within the limits of Max and Min of the smaller table below, and if they do, it publishes the result, if they do not fall within the limits of Max and Min, it leaves it empty. I did this using this formula:
=IF(AND(A3<$B$24,A3>$C$24,B3<$B$25,B3>$C$25,C3<$B$26,C3>$C$26,D3<$B$27,D3>$C$27,E3<$B$28,E3>$C$28),F3,"")
copied the column with the criteria. It works great for what I want to achieve, but as this table grows and I add more columns, it seems to be incredibly inefficient and prone to big human error. Is there a way to achieve the same results, but using a more effective formula?
picture for reference:
