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

J

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
 
View attachment Solution.xls
Junaid said:
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
Click to expand...
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.
 
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.
 
You can change the COUNT() function in the conditional formula to COUNTIF()

e.g.

=AND(COUNTIF($A2:$E2,">0")>2,MIN($A2:$E2)=A2)
 
Thanks a lot. My problem is solved.
Hoping you all the best.
 
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.
 
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.
 
You are great. It works. Thanks for giving your precious time.
 
You must log in or register to reply here.
Back
Top