Minimum Value for more than two non-blank cells in each row.

Junaid

New member
Joined
Feb 20, 2013
Messages
5
Reaction score
0
Points
0
On the sheet there are many rows. There are five columns in each row. But in these columns some cells are blank. I want to get formula for all the rows that will bring minimum value for each row, provided at least three cells are non-blank in that row. If less than three values are available in a row then the formula shall not apply for that row. That is minimum value for a row containing less than three non-blanks cells shall not be highlighted.

EXAMPLE



ABCDE
11020465558
21546226618
33456
4235678
55946
 

peter.abing

New member
Joined
Oct 24, 2012
Messages
34
Reaction score
0
Points
0
View attachment Solution.xls
On the sheet there are many rows. There are five columns in each row. But in these columns some cells are blank. I want to get formula for all the rows that will bring minimum value for each row, provided at least three cells are non-blank in that row. If less than three values are available in a row then the formula shall not apply for that row. That is minimum value for a row containing less than three non-blanks cells shall not be highlighted.

EXAMPLE



ABCDE
11020465558
21546226618
33456
4235678
55946
Please see attached. The shaded cells contain the formula you are looking for.
I've added a conditional formatting which you want. Your example is off. Row one should have 10 in red.
 

Junaid

New member
Joined
Feb 20, 2013
Messages
5
Reaction score
0
Points
0
Your formula helped me a lot,for which I am thankful. However, there is still a problem. If "0" is typed in a blank cell, the formula declares it as minimum value. Please modify the formula to ignore "0" values in any of the cells and consider it as blank cell.
I will be thankful to you.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
You can change the COUNT() function in the conditional formula to COUNTIF()

e.g.

=AND(COUNTIF($A2:$E2,">0")>2,MIN($A2:$E2)=A2)
 

Junaid

New member
Joined
Feb 20, 2013
Messages
5
Reaction score
0
Points
0
Thanks a lot. My problem is solved.
Hoping you all the best.
 

Junaid

New member
Joined
Feb 20, 2013
Messages
5
Reaction score
0
Points
0
Sorry Brother!........It works only if two out of four cells contains data and if one put "0" in the blank cell, then no minimum is shown. However, if three cells have data and you put "0" in the 4th blank cell, then the formula shows "0" as minimum value. Please solve the problem.
 

peter.abing

New member
Joined
Oct 24, 2012
Messages
34
Reaction score
0
Points
0
Change the formula of the conditional formatting to =AND(COUNTIF($A2:$E2,">0")>2,RANK(A2,$A2:$E2,1)-COUNTIF($A2:$E2,"<=0")=1)
This assumes that you only want counting numbers.. Zero and negative numbers will be disregarded.

Feedback if this works.
 

Junaid

New member
Joined
Feb 20, 2013
Messages
5
Reaction score
0
Points
0
You are great. It works. Thanks for giving your precious time.
 
Top