Locating a specific cell
Hi
This is a simplified example of what I am trying to do I want to display the result for the max and min and also the year and month. In this case the min is 2 in Feb 2009 and the max is 9 in Mar 2010 A B C D E 1 Jan Feb Mar 2 2009 3 2 7 3 2010 5 4 9 -Regards bookman |
Locating a specific cell
Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" For MIN() try the below formulas... =INDEX(A1:E1,MIN(IF(C2:E3=MIN(C2:E3),COLUMN(C2:E3) ))) =INDEX(B:B,MIN(IF(C2:E3=MIN(C2:E3),ROW(C2:E3)))) For MAX() =INDEX(A1:E1,MIN(IF(C2:E3=MAX(C2:E3),COLUMN(C2:E3) ))) =INDEX(B:B,MIN(IF(C2:E3=MAX(C2:E3),ROW(C2:E3)))) -- Jacob (MVP - Excel) "bookman3" wrote: Hi This is a simplified example of what I am trying to do I want to display the result for the max and min and also the year and month. In this case the min is 2 in Feb 2009 and the max is 9 in Mar 2010 A B C D E 1 Jan Feb Mar 2 2009 3 2 7 3 2010 5 4 9 -Regards bookman |
All times are GMT +1. The time now is 02:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com