Ignoring Blank Cells in IF function

[Kayla09]

I'm having trouble getting my formula to ignore blank cells.

If any of the cells A4:A8 are less than 600, I need the formula to return TD. If none are below 600, I need to do a vlookup with the average of A4:A8 to determine the score.

However, not all the cells A4:A8 need to be filled in. If only 3 are filled in, my formula assumes the blanks are zeros and gives a TD rating. How do I get it to ignore the blank cells?

Please Login or Register  to view this content.

Consumer Score.xlsx

Thanks in advance!

[Teethless mama]

=if(min(a4:a8)<600,"td",lookup(b10,a16:b21))

[Fotis1991]

One "ugly" way

=IF(OR(AND(A4<600,A5<600,A6<600,A7<600,A8<600),AND(A4<>"",A5<>"",A6<>"",A7<>"",A8<>"")),"TD",VLOOKUP(B10,A16:B21,2))

This gives 3, as result in your example.

[Kayla09]

Wow. I'm embarrassed at how easy that was. Thanks for the help!

[bebo021999]

Simplying the formula:
=IF(MIN(A4:A8)<600,"TD",VLOOKUP(B10,A16:B21,2))

[Teethless mama]

You're Welcome!

[Dunc3142]

This might work for you...

=IF(COUNTIFS(A4:A8,">=600")<3,"TD",VLOOKUP(B10,A16:B21,2))