Conditional Format For Lowest When There Are Blanks

I have s sheet that lists 28 rows of 10 scores across where the data in the columns are added each week. From the 8th week to the 10th week, I want to highlight the lowest score as follows:

Eighth Week- one lowest score; Ninth Week- two lowest scores; Tenth Week- three lowest scores.

I am having problems making the correct Conditional Formatting code.

Suggestions would be greatly appreciated.

Thank you both for your suggestions. However, I may have not explained my goal properly. The sheet looks similar to benishiryo's attachment. The goal is to highlight the three lowest scores for the entire ten weeks. The low score would include all scores from week one to week ten. I am very sorry for the incorrect explanation.

Thanks

Originally Posted by NBVC

Using benishiryo's attachment there are 5 instances of 1 (being the lowest score).. do you want those to be what is highlighted?

When there are eight scores (week 1 thru 8) weeks I need to highlight the one lowest score. When there are nine scores (1 thru 9 weeks) I need the two lowest scores. And when there are all ten scores posted (1 thru 10 weeks) I need the three lowest scores highlighted. To make it a little more confusing is that sometimes there are no scores posted for a particular week for a particular name. So if there is one blank when we get to the 8th week, I don't need a score highlighted.

The premise is that we only use the seven highest scores out of a possible ten. I want to highlight the three lowest scores to indicate that they are not being used. But, there will be some names that won't have but seven scores out of the ten weeks if they were absent three weeks and have no scores posted. In that case there would be no low score to indicate because all seven would be used. In other words, a name needs a minimum of seven scores, and if they have more, we throw the lowest out...up to a possible three being thrown out.

Perhaps I should just do it individually without a conditional format code. It does take much time though.

Thanks

Attached is what I want to accomplish. Each name only qualifies if he has a minimum of seven scores, AND only the highest seven out of the ten count. I want to show the low score(s) that will not be used by highlighting. As the weeks progress, some names may have filled all the scores, while others may not. We can't count a blank week as a score.

Originally Posted by benishiryo

ok try highlighting B2:K29 & use this formula in the Conditional Formatting:
=AND(IF($L2=8,SMALL($B2:$K2,1)>=B2,IF($L2=9,SMALL($B2:$K2,2)>=B2,IF($L2=10,SMALL($B2:$K2,3)>=B2))),B2<>"")

There may be an error in the code because it isn't accepted.

Originally Posted by benishiryo

must have accidentally typed a space

Please Login or Register  to view this content.

Fantastic!!! I am going to study this one to break down the elements. Great job. It does exactly what I was after.

Thanks so much.

Originally Posted by NBVC

Alternatively:

=AND(SMALL($B2:$K2,CHOOSE($L2-7,1,2,3))>=B2,B2<>"")

This works great too. In fact, whenever we can get the required results using fewer keystrokes, makes us happier. I never would have thought of using the CHOOSE function. Very efficient.

Thanks for your help.