mercredi 30 mars 2016

Excel, more efficient formula for multiple IF ANDS

I have a spreadsheet that I am making that looks 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    

Under the headings Index, Diff, Exc, Sym, Sec, Result is all data, In the criteria met column i have a formula that checks if the prior headings fall within the Max and Min limits of the smaller table underneath, and if they do it posts the result, if they dont all fall within the Max and Min boundaries it leaves it blank. I did that by 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 down the criteria met column. It works perfectly fine for what I want it to achieve but as this spreadsheet grows and I add more columns it seems like it will be incredibly inefficient and prone to alot of human error. Is there a way to achieve the same results but by using a more efficient formula?

a picture for reference as well: enter image description here

Aucun commentaire:

Enregistrer un commentaire