Display minimum of numbers or text if range criteria is met

nbawa27

New Member
Joined
Jul 28, 2011
Messages
2
=IF(AND(A4:A6>=0,A4:A6<=5,COUNTIF(A4:A6,"NS")),MIN(A4:A6),"NS")

This equation doesn't seem to capture the full logic of what I'm trying to accomplish. In column A, we have a validated data cell where the only allowed values are: 1,3,5, NS. In the column where the above equation fits, I would like the minimum value of the range to be displayed unless all of the range values are "NS". If the range has any combination of values including "NS" and numbers, only the minimum of the numbers should be displayed. Again, the equation should produce "NS" only if all of the range values are "NS".

Any help would be very much appreciated. Thank you in advanced for your efforts!
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,707
Welcome to the Board!

If I follow, try:

Code:
=IF(COUNTIF(A4:A6,"NS")=3,"NS",MIN(A4:A6))
Matty
 

nbawa27

New Member
Joined
Jul 28, 2011
Messages
2
Matty,

Thank you for the welcome. That is a brilliant solution. I appreciate your prompt and helpful reply. Hope I can repay one soon.
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,707
Matty,

Thank you for the welcome. That is a brilliant solution. I appreciate your prompt and helpful reply. Hope I can repay one soon.
Glad I could help.

Matty
 

Forum statistics

Threads
1,082,569
Messages
5,366,360
Members
400,886
Latest member
Fchel

Some videos you may like

This Week's Hot Topics

Top