Easiest Way to Return #s within 2

[SEAL Cmdr]

Trying to evaluate 4 groups (columns of data), and formula only return results that are within 2 (+/- 2) of others in a group. Answer should just be 8 - 20, but AND formula returning 1 - 27 instead. Any ideas on how to fix the formula?

I'm not sure how to show a picture of the doc, but posted a link to Dropbox below:

https://www.dropbox.com/s/7towvvs8187q0vx/Formula Student Evaluation.xlsx?dl=0

Thanks.

 

[joeu2004]

Trying to evaluate 4 groups (columns of data), and formula only return results that are within 2 (+/- 2) of others in a group. Answer should just be 8 - 20, but AND formula returning 1 - 27 instead. Any ideas on how to fix the formula?

I'm not sure I understand your intent. But the following change in F13 (copy down the column) gives the result you require:

=IF(AND(COUNT(B11:B15)>0,COUNT(C11:C15)>0,COUNT(D11:D15)>0,COUNT(E11:E15)>0),A13," ")

Unrelated observation.... Some of the "blank" cells in columns B:E actually contain a string with a single blank, like " " (space in between) in formula above. That makes it difficult to test for a "blank" cell; that is, a cell that appears blank. IMHO, it is better to use the null string "" (pair of double-quotes with no space in between). Then a formula like IF(B13="", "blank", "not blank") does what you expect.

 

[SEAL Cmdr]

Joe,

You are an analytical genius. It worked indeed. Thank you very much. Hopefully I can return the favor one day, but you're probably way ahead of me...Nonetheless, thanks again.

SEAL