Excel formula question

[TravelMI]

Hi,

I need a formula that will do the following, =IF(A1≥300,"YES","NO") but cannot seem to get it working.

The formula essentially needs to read A1, ignore the first two characters of the cell and output YES if the number is more than or equal to 300.

Below is an example of the data set and my desired output. NOTE: NA needs to be accounted for and should be output as "NO".

Any insight into formula to try would be greatly appreciated!

AA150	NO
BB200	NO
CC300	YES
DD299	NO
NA	NO

 

[kweaver]

One way (assuming there are always 2 leading chars):

=IF("NA"=LEFT(A1,2),"no",IF(1*RIGHT(A1,LEN(A1)-2)>=300,"yes","no"))

Another way (not required to have only 2 leading chars):

=IFERROR(IF(VALUE(RIGHT(A1,LEN(A1) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A1&"0123456789")) +1))>=300,"yes","no"),"no")

 

[kweaver]

BTW, if you have 2016 or later you can use TEXTJOIN:

=IFERROR(IF(1*TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))>=300,"yes","no"),"no")

 

[TravelMI]

Thank you and yes there are always at least two characters (leading or just NA) so that first formula seems to work nicely. Thanks again!