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

 

[iancl23]

Hi All,
I can't get any of the ones that do the entire sheet working? And the individual row one at the beginning does work however if there are more than one of the biggest number in the smallest 5 it highlights 6 numbers for example if the row is 1,1,2,3,4,4,5,10,10,10 it will highlight the 1,1,2,3 and both 4s?
Sorry for being a pain in the ar*e
Regards Ian

 

[Peter_SSs]

if there are more than one of the biggest number in the smallest 5 it highlights 6 numbers for example if the row is 1,1,2,3,4,4,5,10,10,10 it will highlight the 1,1,2,3 and both 4s?

You can see with our examples that is not the case. In row 3 the 5th highest "Points" value is 10 and there are quite a few of them but only 2 are highlighted to make a total of 5 highlights only.
So it is probably an implementation issue at your end.
We cannot see your row or column labels so we don't actually know where your data is.

For our layout you would select B3:U6 and apply the CF rule given.
Note that the selection starts in column B even though that is not a "Points" column.

If you are still having difficulty it would help if you could post a small example like we have with XL2BB
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)