Hi All,
I am new to the site but I am trying to find a min and a max value in excel and have a formula that seems to work 90% of the time but if a text string has the number I am trying to filter by in it then it could potentially return the wrong value. See my example table below.
I am able to search the data above using the following criteria
Max (Returns 77 not 39) - =MAX(IF(ISNUMBER(SEARCH(A2,$A3:$A100)),$B3:$B100, ""))
Min (Returns 5 not 35)- =MIN(IF(ISNUMBER(SEARCH(A2,$A3:$A100)),$B3:$B100, ""))
Any guidance you can provide would be greatly appreciated.
I am new to the site but I am trying to find a min and a max value in excel and have a formula that seems to work 90% of the time but if a text string has the number I am trying to filter by in it then it could potentially return the wrong value. See my example table below.
Character | Max | min | |
66 | 77 | 5 | |
Dataset | value | ||
66 aaa | 35 | ||
66 aaa | 39 | ||
366 aaa | 75 | ||
366 aaa | 77 | ||
566 aaa | 5 | ||
566 aaa | 10 |
I am able to search the data above using the following criteria
Max (Returns 77 not 39) - =MAX(IF(ISNUMBER(SEARCH(A2,$A3:$A100)),$B3:$B100, ""))
Min (Returns 5 not 35)- =MIN(IF(ISNUMBER(SEARCH(A2,$A3:$A100)),$B3:$B100, ""))
Any guidance you can provide would be greatly appreciated.