Highlight the lowest 5

[iancl23]

How do I highlight the 5 values (used in the sum) in this formula =SUMPRODUCT(SMALL(IF(MOD(COLUMN(B3:U3),2)=1,B3:U3),{1,2,3,4,5}))
I've tried typing this formula in to conditional formatting however it won't work, BTW I'm a complete novice using formulas in excel
Regards Ian

 

[Kaper]

Welcome to the forum Ian,

Select B3:U3 and use such formula to create a rule for conditional formatting:

=AND(B3<=SMALL(IF(MOD(COLUMN($B3:$U3),2)=1,$B3:$U3),5),MOD(COLUMN(B3),2)=1)

Note that if you could have duplicates in row 3 the formula shall be more complicated (then you shall decide which values shall be highlighted - these from left side?). Anyway may be the values do not repeat, or if they do, you can have more than 5 highlighted by Conditional Formatting.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2
3		2	4	6	8	10	12	14	16	18	20	1	3	5	19	17	15	13	11	9	7
4
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:U3	Expression	=AND(B3<=SMALL(IF(MOD(COLUMN($B3:$U3);2)=1;$B3:$U3);5);MOD(COLUMN(B3);2)=1)	text	NO

 

[iancl23]

Thanks for your speedy reply, I'll give that a go when I'm back Infront of my laptop, Is there a way to apply this formula to a number of rows in a table or will I have to do them all separately?
Thanks Ian

 

[Peter_SSs]

Welcome to the MrExcel board!

Do columns B:U have headings? If so, what are they? Headings can often simplify and/or make more robust formulas that need to reference every nth column.
Here is an example. The benefit of this type of formula is that if a column is inserted (or deleted) at the left of the data, the correct values would still be highlighted.

25 02 18.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Jan		Feb		Mar		Apr		May		Jun		Jul		Aug		Sep		Oct
2	Budget	Actual	Budget	Actual	Budget	Actual	Budget	Actual	Budget	Actual	Budget	Actual	Budget	Actual	Budget	Actual	Budget	Actual	Budget	Actual
3	2	4	6	8	10	12	14	16	18	20	1	3	5	19	17	15	13	11	9	7
4	13	13	1	1	4	15	6	20	15	15	8	10	11	3	20	8	20	20	6	5
5	15	6	17	17	3	3	15	12	1	8	3	7	4	7	2	4	3	7	17	14
6	15	15	3	15	8	12	8	14	9	1	19	15	4	6	10	16	14	17	18	13
Lowest 5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:U6	Expression	=AND(B$2="Actual",B3<=SMALL(FILTER($B3:$U3,$B$2:$U$2="Actual"),5))	text	NO

 

[iancl23]

This is the spreadsheet I'm working on, the Best 5 is the lowest 5 values in the points columns the weight is the sum of the weights of the 5 lowest points in the adjacent column ( this is a sum I do manually because I'm not god enough to do a formula for this lol, There are a total of 36 rows.
The fill is done manually at the moment
Regards Ian

 

[Kaper]

So I assume this shall always be just 5 cells.
And Adam Wakelin, who has 6 marked blue is just a mistake.
But let's see on James Dent. He has got three 10s and you marked just one in the middle (round 5) but not round 4 or 7. Any rule here?

 

[iancl23]

Adam is indeed a mistake,
James is counting a 10 as one of his lowest 5 it could be any of the three of them
Regards Ian

 

[iancl23]

Thanks for all the help everyone, much appreciated
Regards Ian

 

[Kaper]

The conditional formatting formula can be for sure shortened. Especially if you decide to give up with Excel 2013 and stick to 365.
Anyway, it seems it's working as expected now.

It's a long one. Select B3 to U_lastline and apply formating rule based on formula:

=AND(B$2="Points";OR(AND((COUNTIFS($B3:$U3;"<" & SMALL(IF($B$2:$U$2="Points";$B3:$U3);5);$B$2:$U$2;"Points")+COUNTIFS($B3:B3;SMALL(IF($B$2:$U$2="Points";$B3:$U3);5);$B$2:B$2;"Points"))<=5;B3=SMALL(IF($B$2:$U$2="Points";$B3:$U3);5));B3<SMALL(IF($B$2:$U$2="Points";$B3:$U3);5)))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		Round1		Round2		Round3		Round4		Round5		Round6		Round7		Round8		Round9		Round10
2	Angler	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points
3		2	1	6	1	10	12	14	1	18	10	1	10	5	10	17	10	13	10	9	10
4		13	13	1	1	4	15	6	10	15	15	8	10	11	3	20	8	20	20	6	5
5		15	6	17	17	3	3	15	12	1	8	3	7	4	7	2	4	3	7	17	14
6		15	15	3	15	8	12	8	14	9	1	19	15	4	6	10	16	14	17	18	13
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:U6	Expression	=AND(B$2="Points";OR(AND((COUNTIFS($B3:$U3;"<" & SMALL(IF($B$2:$U$2="Points";$B3:$U3);5);$B$2:$U$2;"Points")+COUNTIFS($B3:B3;SMALL(IF($B$2:$U$2="Points";$B3:$U3);5);$B$2:B$2;"Points"))<=5;B3=SMALL(IF($B$2:$U$2="Points";$B3:$U3);5));B3<SMALL(IF($B$2:$U$2="Points";$B3:$U3);5)))	text	NO

 

[Peter_SSs]

Here is a slightly shorter one that I think works in 2013

iancl23.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Round1		Round2		Round3		Round4		Round5		Round6		Round7		Round8		Round9		Round10
2	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points
3	2	1	6	1	10	12	14	1	18	10	1	10	5	10	17	10	13	10	9	10
4	13	13	1	1	4	15	6	10	15	15	8	10	11	3	20	8	20	20	6	5
5	15	6	17	17	3	3	15	12	1	8	3	7	4	7	2	4	3	7	17	14
6	15	15	3	15	8	12	8	14	9	1	19	15	4	6	10	16	14	17	18	13
Lowest 5 (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:U6	Expression	=AND(B$2="Points",OR(B3<SMALL(IF($B$2:$U$2="Points",$B3:$U3),5),AND(B3=SMALL(IF($B$2:$U$2="Points",$B3:$U3),5),COUNTIFS($B$2:$U$2,"Points",$B3:$U3,"<"&SMALL(IF($B$2:$U$2="Points",$B3:$U3),5))+COUNTIFS($B$2:B$2,"points",$B3:B3,B3)<=5)))	text	NO

.. and a shorter one again for 365

iancl23.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Round1		Round2		Round3		Round4		Round5		Round6		Round7		Round8		Round9		Round10
2	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points	Weight	Points
3	2	1	6	1	10	12	14	1	18	10	1	10	5	10	17	10	13	10	9	10
4	13	13	1	1	4	15	6	10	15	15	8	10	11	3	20	8	20	20	6	5
5	15	6	17	17	3	3	15	12	1	8	3	7	4	7	2	4	3	7	17	14
6	15	15	3	15	8	12	8	14	9	1	19	15	4	6	10	16	14	17	18	13
Lowest 5 (4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:U6	Expression	=LET(t,"Points",p,TAKE(SORT(FILTER($B3:$U3,$B$2:$U$2=t),,,1),,5),AND(B$2=t,B3<=MAX(p),COUNTIFS($B$2:B$2,t,$B3:B3,B3)<=COLUMNS(FILTER(p,p=B3))))	text	NO