Auto value colour display with =sum - small formula

Hi All,

I am hoping you guys can help me out here as i have been tearing my hair out over this one. i have an excell sheet with the following fields:

R.1 R.2 R.3 R.4 R.5 R.6 TOT PTS
78 63 53 66 47 0 307

Now upto here easy enogh, my TOT PTS filed is populated with the following formula: =SUM(F3:K3)-SMALL(F3:K3,1), basically adding the 5 biggest values out of R1:R:6, now the tricky part that i can t figure out, how do i get the smallest value, therefore the one which is taken out by -SMALL(F3:K3,1) in the formula, to be automatically displayed in RED???

I have tried using conditional formatiing but my knowledge on that is very slim so any advice is welcome. Thanks.

Select the range, F3:K3 and invoke Conditional Formatting..

Choose Formula Is and enter formula:

=F3=Small($F3:$K3,1)

or

=F3=Min($F3:$K3)

Click Format to set color

You would highlight F3:K3 (selecting F3 first) and then setting the Conditional Formula to be:

=AND(F3=SMALL($F3:$K3,1),COUNTIF($F3:F3,F3)=1)
setting font to red

the COUNTIF is to avoid the smallest value being highlighted multiple times - only the first instance of the low number will highlight.

Did you try DonkeyOte's formula? I think that is exactly what it does.

I think rather both 0's should be highlighted rather than 0 & one instance of 1, no ?

Assuming values in F3:Q3 then with range highlighted (having selected F3 first), formula would be:

Please Login or Register  to view this content.

yes, it will highlight the lowest 2 values in order - once 2 values are found no others will be formatted.

Yes, that is correct.