Broken formula with IF, AND and OR

[Espleyville]

Good evening,

I am sure I'm a noob and am missing something obvious, but I managed to get this formula to work, then I filled it down to the whole column and it stopped working (?!) and now I can't get it to work at all.

The formula is: =IF(OR(D4="HRW",(AND(NOT(I4=""))*(I4>=79.9%))),"YES","NO")

D column is a text category (resulting from another formula)
Column I contains ranked results from column E as a percentage, where column D is one of the three text categories (and so has a lot of blanks where the row has a different text category in D).

What I am trying to achieve is a formula that produces a "YES" where EITHER 1) Entry for column D is 'HRW', or 2) the entry in column I is not blank and above 80%.

For a bit of context, this is pretty much my first time using any of these three functions, so please take pity on me...

Many thanks in advance.

 

[Michael M]

If I5 is always going to be a number or blank, try.....

=IF(OR(D5="HRW",I5>=79.9%),"YES","NO")

 

[Espleyville]

If I5 is always going to be a number or blank, try.....

=IF(OR(D5="HRW",I5>=79.9%),"YES","NO")

Hi Mike,

Thanks for your reply - unfortunately, the formula identifies the blank cells in column I as being greater than 79.9% due to letters ranking greater than numbers

Column I contains the following code: =IF(D3="TC2",RANK.EQ(E3,$E$3:$E$550,1)/COUNT($E$3:$E$550),"")

If column D has a particular text category (TC2) it ranks the values of column E as a percentage. It's essentially the null text string caused by this formula that is causing me the issue, resulting in having to use the AND and OR functions to try and exclude blank cells.

Many thanks

 

[sparky2205]

Give this a shot:

=IF(OR(D4="HRW",(AND(NOT(I4=""),I4>80%))),"YES","NO")