Results 1 to 8 of 8

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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



    A B C D E
    1 10 20 46 55 58
    2 15 46 22 66 18
    3 34 56
    4 23 56 78
    5 59 46

  2. #2
    Solution.xls
    Quote Originally Posted by Junaid View Post
    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



    A B C D E
    1 10 20 46 55 58
    2 15 46 22 66 18
    3 34 56
    4 23 56 78
    5 59 46
    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.

  3. #3
    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.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    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)


  5. #5
    Thanks a lot. My problem is solved.
    Hoping you all the best.

  6. #6
    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.

  7. #7
    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.

  8. #8
    You are great. It works. Thanks for giving your precious time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •