Get The Large Number From Range By Skipping Values Based On A Condition

[ashu1990]

Hello guru's,

i am not sure if it can be done but any thing is possible for the guru's

Column A Column B
I 10
I 90
A 30
B 40
C 50
I 60
D 80

i want the A,B,C,D large numbers but excluding the I into the range

the formula so far i have tried is "=IF(A1="I",OFFSET(A1,1,0),LARGE($B$1:$B$7,1)) "

i am really clueless can any one help me?

i have also posted it in some other help source
http://http://www.ozgrid.com/forum/s...d.php?t=186605

[Rick Rothstein]

i have also posted it in some other help source
http://http://www.ozgrid.com/forum/s...d.php?t=186605

For those who are interested, the above link does not work but this one does...

formula to skip a text and get the large number into the range

Also note that the OP received the answer he was seeking in that other forum.

[Excel Fox]

For those who are interested, in case the objective is to find the largest value, after skipping the values for all the unwanted text (in this case, 'I'), then one can use this formula as well

=MAX(B1:B7*(A1:A7<>"I")) (As Array or in other words, CTRL+SHIFT+ENTER formula)