Rank, select, conditional format

One way:

=MIN($A$3:$A$6)+MIN(IF($A$3:$A$6<>MIN($A$3:$A$6),$B$3:$B$6))

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

You can use the two halves of the formula for your CF rules.

An alternative for you... just ENTER, no array entry required.

=MIN(A3:A6)+AGGREGATE(15,6,B3:B6/(A3:A6<>MIN(A3:A6)),1)

Mine will have the same problem, so modify to:

=MIN(A3:A6)+AGGREGATE(15,6,B3:B6/((A3:A6<>MIN(A3:A6))*(B3:B6<>MIN(A3:A6))),1)

Change the B10 CF rule to this:

=AND(B10=MIN(IF($A$10:$A$13<>MIN($A$10:$A$13),$B$10:$B$13)),COUNTIF($B$10:$B10,MIN(IF($A$10:$A$13<>MIN($A$10:$A$13),$B$10:$B$13)))=1)

You can do something similar with the A10 rule.

Hi. I will not be using Ali's formula... that's up to her. i used my own version. Done for the first two blocks, so you can see what needs to be done.

Whatever. I've left now. I daresay Ali will be back later and will sort you out.

I’ll have a look in the morning - can’t do conditional formatting on the iPad!

You will need just TWO rules for each block. In each case, have the first cell in the block of four selected as you set the rule.

Excel 2016 (Windows) 32 bit

	E	F
3	=AND(A3=MIN($A$3:$A$6),COUNTIF($A$3:$A3,MIN($A$3:$A$6))=1)	Set for A3, apply to A3:A6

Sheet: Test Chart

Excel 2016 (Windows) 32 bit

	J	K
3	=AND(B3=MIN(IF($A$3:$A$6<>MIN($A$3:$A$6),$B$3:$B$6)),COUNTIF($B$3:$B3,MIN(IF($A$3:$A$6<>MIN($A$3:$A$6),$B$3:$B$6)))=1)	Set for B3, apply to B3:B6

Sheet: Test Chart

Excel 2016 (Windows) 32 bit

	E	F
10	=AND(A10=MIN($A$10:$A$13),COUNTIF($A$10:$A10,MIN($A$10:$A$13))=1)	Set for A10, apply to A10:A13

Sheet: Test Chart

Excel 2016 (Windows) 32 bit

	J	K
10	=AND(B10=MIN(IF($A$10:$A$13<>MIN($A$10:$A$13),$B$10:$B$13)),COUNTIF($B$10:$B10,MIN(IF($A$10:$A$13<>MIN($A$10:$A$13),$B$10:$B$13)))=1)	Set for B10, apply to B10:B13

Sheet: Test Chart

And so on.

Follow-On Query from Thedunna:

In my Test Worksheet I am looking to auto populate the column shaded in gray with a 1 or a 2 based on the following rules:

1. FIRST take the minimum value under the letter "G" and give that a value of "1".
2. Not using the value under the letter "N" next to the value used under "G", take the minimum value under "N" from the remaining 3 numbers and give that a value of "2".

If there are duplicate minimum numbers under "G", use the one which allows you to use the smallest number under "N". I hope this is doable. I manually entered the 1's and 2's in the first 9 instances to show what the results should be.

Thank you...

Follow-On Query:

=IF(AND(C2=SMALL(C$2:C$5,1),COUNTIF(C$2:C2,SMALL(C$2:C$5,1))=1),1,IF(AND(COUNTIF(A$6:A6,2)=0,B2+C2=MIN(IF(ROW(A$2:A$5)-1<>MATCH(MIN(C$2:C$5),C$2:C$5,0),B$2:B$5+C$2:C$5))),2,""))

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Slight error in formula. Change to this:

=IF(AND(C2=SMALL(C$2:C$5,1),COUNTIF(C$2:C2,SMALL(C$2:C$5,1))=1),1,IF(AND(COUNTIF(A$1:A1,2)=0,B2+C2=MIN(IF(ROW(A$2:A$5)-1<>MATCH(MIN(C$2:C$5),C$2:C$5,0),B$2:B$5+C$2:C$5))),2,""))

then copy paste to each top box and copy down.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Are we done here? You still haven't marked the thread as solved ...

It's nice to know when you've spent about three hours working it out for someone.

Knowing that it all works is what matters - thank you for letting us know. I am sure some of the wizards here would have made light work of it, but it became a bit of a mission for me over coffee this morning. A nice way to exercise the grey cells over half term! I am really glad it worked.