Conditional Formatting with LARGE formula not working

[peri1224]

This is a formula for calculating the best (lowest) 4 out of 6 results, and it works well :
=SUM(SMALL((E3,G3,I3,K3,M3:O3),{1,2,3,4}))

Formatting this range is not yet working right. The attached sample file shows a range (D3:O8) that needs to be conditionally formatted.
Non-attendance scores 0 score = 25 ranking points (R). If that applies, both of the cells must be shaded grey for each cup. That is presently done with :
=AND($D4=0,$E4=25) applies to $D$4
=AND($D4=0,$E4=25) applies to $E$4
copied down, and repeated for all 6 column pairs. Probably there is a more elegant way to do one formula for the whole range. But I'm still looking for it.

The 2 highest ranking points (R) out of the 6 cups should be highlighted yellow (overriding any grey shading that may be there). Yellow indicates that they were not included in the total calculation.

Attempted to do the formatting this way by adapting the above SMALL formula :
=LARGE((E3,G3,I3,K3,M3:O3),{1,2})
in the conditional formatting formula entry, but a message says :
You may not use unions, intersections or array constants for Conditional Formatting criteria.
Looking for a way that is acceptable.

[teylyn]

What exactly is your formula in the condition? Both the SMALL and the LARGE combinations you gave above will return numbers. For conditional formatting you will need a formula that returns True or False, so I can't quite see how your SMALL formula would return a meaningful result.

Can you post a workbook?

For the LARGE formula, I suggest you leave the array out of the function and use LARGE in its native form, just create two individual conditions, like

=A1=LARGE((E3,G3,I3,K3,M3:O3),1)

=A1=LARGE((E3,G3,I3,K3,M3:O3),2)

[DonkeyOte]

Correct, in line arrays can not be used in Conditional Formatting.

Before getting into the merits of replacements - you should consider the fact that either of LARGE 1 / 2 values may appear multiple times such that they are both included & excluded simultaneously...

Consider:

5
5
5
5
5
5

It follows that 4 of the above are "included" and two are "excluded" yet each instance would be considered to be >= 2nd largest value in the set.

So it's not quite so trivial as you may think.... you need to ensure that both

a) value >= 2nd largest value

b) count of current value up to and including current column is such that this instance must be one of those 2 values excluded

Does that make sense ?

[peri1224]

=A1=LARGE((E3,G3,I3,K3,M3:O3),1)

=A1=LARGE((E3,G3,I3,K3,M3:O3),2)

This still results in the same error message about array constants, see 1st post.
Attached is the sample file.

[peri1224]

Correct, in line arrays can not be used in Conditional Formatting.

Before getting into the merits of replacements - you should consider the fact that either of LARGE 1 / 2 values may appear multiple times such that they are both included & excluded simultaneously...

Yes, have considered that and didn't know how that will be handled, but never considered it trivial. Apparently, if the same number appears 3 times or more it is considered the same rank and would also be part of the shading. Of course, in this case that would be wrong, as only 2 numbers should be excluded, e.g. if there are 3 instances of 0 25, only two must included for the yellow shading.
Same goes for the =SMALL formula, where that can also happen. So, if the unlikely but possible case of 5 or 6 same numbers were to appear, he should only take 4 of them and exclude 2, which ones doesn't matter.

[DonkeyOte]

Yes, I thought I'd nudged you towards a possible solution with:

you need to ensure that both

a) value >= 2nd largest value

b) count of current value up to and including current column is such that this instance must be one of those 2 values excluded

So that's your Conditional Formatting logic...

Again you're correct that you can't use LARGE in this context given the non-contiguous ranges so you must consider an alternative route such as SUMPRODUCT test ... or as before moving the data into a contiguous data set.

Goes without saying that contiguous data sets are generally easier to work with.

[daddylonglegs]

=A1=LARGE((E3,G3,I3,K3,M3:O3),1)

You can't use unions in CF either, this part

(E3,G3,I3,K3,M3:O3)

is a union

To highlight the cells you want try this:

Select range E3:O8 and use this formula

=(E$1="R")*(E3>=LARGE(IF($E$1:$O$1="R",$E3:$O3),2))*(SUM(($E$1:E1="R")*($E3:E3>= LARGE(IF($E$1:$O$1="R",$E3:$O3),2)))<=2)

[peri1224]

Have tried moving the data into contiguous columns but the results are not as expected.
I think you are trying to nudge me into territory that is alien to me. Cannot make sense of the SUMPRODUCT explanations and examples. Can't create such heavy stuff (for me) yet...