Col A - Name
Cols B-O - Score
Col P - The total of B-O dropping the two lowest scores (using SMALL).
No problems so far.
What I’d like to do is set a conditional format such that if the score in a particular column B-O is being dropped, it would be formatted (strikethrough probably). But in the case of ties (for example, if all cols B-O have a 95) I only want two of them to strikethrough since the other 12 ‘count’. I can easily set it so that only the first instance of any particular score formats with strikethrough (using MATCH), but I’ll be darned if I can figure out to do the second.
So simple examples:
Row 2 has 4 95s, 1 90, and 1 89. The 90 and 89 should strikethrough.
Row 3 has 4 95s, 2 90s, and 1 88. The 88 and first 90 should strikethrough.
Row 4 has 6 95s. First two 95s should strikethrough.
How does you decide a score is “dropped” – is this done by a calculation on the spreadsheet? Are you doing dropping the two low scores en every instance?
Also, does it matter if the strikethroughs change over time? For example, if you format the cells so the lowest score in a range is struck out, that will happen immediately as you enter each score, and a lower score added at a later time will un-strike the earlier score and strike out the new low score. IOW, do you want all the scores entered before the decision is made which two to strike out?
That’s a simplified version, but that’s the guts of it.
And I specifically want the strikethroughs to change over time. I add new values in from time to time and want them to reflect the current state of the ignored scores.
I think you’re not gonna be able to do this neatly without some programming. The central challenge is that SMALL returns the *value *of the cell it matches, rather then the *location *of the cell it matches. So there’s no way to piggy-back on that to set the correct cell(s) formatting in the event of a tie. If by the constraints of yuor problem a row of scores could never contain ties you’d already be done. But it appears you don’t have that constraint.
Here is a massively evil hack which will work provided the scores are constrained to be non-neagtive integers. Or can be scaled to all be non-negative integers. I’m going to describe this at a pretty high level of abstraction. If you need point and click instructions, ask back …
The big idea is to add a fixed fractional tiebreaker value to each score by column. That way there are no ties & therefore SMALL & MATCH can always work unambiguously.
In a bit more detail: Add a header row with a small fractional number, e.g. .01, .02, .03, etc., to each score column. These values must all be less than 1. Then perform your SMALL & MATCH calcs in each row on the sum of the input score and that column’s header value.
You might find it easiest to create a second table of adjusted scores where each cell is the actual input score plus the column header tie-breaker. Then do your total effective score on the input table values as you are now (to avoid having the tie breaker additives accumulating into the SUM function), but do the conditional fomatting of the input table using the SMALL & MATCH calcs on values in the the secondary table.
The only hassle is that if you want to add a column to the main table to represent a new set of scores you also need to add & populate a corresponding column in the secondary table.
I don’t do a lot of conditional formatting, so you may find propagating the conditional formatting rules to all the cells of the input table to be a bitch with the setup I described above. if so, you might try using array formulas rather than ranges as inputs to SMALL & MATCH, and forgoing the secondary score table altogether.
Array formulas are pretty advanced topics, but can do amazing things when ranges aren’t cooperating.
LSLGuy, thank for the suggestion. It was creative and I probably could have made that work. Unfortunately, I can’t mess with the raw scores, they’re a look up from elsewhere and I can’t touch the original (it’s averaged, amongst other things). Great hack though and I will keep that in mind.
Very familiar with array formulas and I use them constantly. Yes, they are a pain in the ass, but until they allow actual formulas into SUMIF/COUNTIF/etc, then there are cases where they are the only way to roll.
If you are willing to use a second table you can use the rank function to calculate the values to be dropped. Rank does not really work for duplicates, but you can easily work around this by adding a position specific modifier.
Assuming you original table is in B2:G4 and the second table in J2:O4 the formula for J2 would be
=RANK(B2,$B2:$G2,1)+1/(COUNT($B2:$G2)+2-COUNT($B2:B2))
The conditional format formula for B2 is
=J2<=SMALL($J2:$O2,2)
Here’s a way that is less of a hack (which is not to deride LSLGuy’s excellent hack): For each score cell, add the number of scores to its left which are less than or equal to it and the number of scores to its right which are less than it. If this is less than 2, then this cell is one of the ones to get formatted.
Format your score table by placing this into the upper-left score’s conditional-format formula (here I assume this score is at cell B2 and that the scores run from columns B to O in one row):
and then use the Format Painter to copy this format into all of the score cells. (The ISBLANK check just prevents the blank scores from having any visible format.)
I forgot to say that the formula above is not actually correct for the leftmost and rightmost columns (B and O in the example); Excel interprets the range B2:A2 not as the empty range but as A2:B2. You can fix that by using a different format for the left and right columns (dropping the inapplicable COUNTIF) or wrapping the COUNTIFs in their own IF column checks.
Thanks, man! I found a version of your formula that works by itself for columns 2-14, and wrapped in an IF with a version just for column 1 works great. No hacks and no extra values needed. (Not that I’m ungrateful to those who offered those solutions - really. I’ve hacked my way past more Excel problems that I can count!). I was helped by the fact that column A contains text so the fact the formula “overlaps” column B didn’t hurt me.