[SOLVED] Auto value colour display with =sum - small formula

Bartholemu · #1 06-23-2009, 10:23 AM

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.

NBVC · #2 06-23-2009, 10:37 AM

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

DonkeyOte · #3 06-23-2009, 10:40 AM

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.

Bartholemu · #4 06-24-2009, 11:25 AM

hi guys,

Thanks for your answer much appriciated, getting closed to what i m looking for :D

Now lets imagin that the min value in the fileds is repeated more than once such as;

R1 R2 R3 R4 R5 R6 R7 R8 R9 R10 R11 R12
0 0 45 35 55 87 14 0 69 87 11 49

where the min value is 0 and is repeated a number of times, with the above formula provided, i makes all the 0s go RED, is it possible for only 1 of them (the first instance of the smallest value) to be displayed in red, and the rest to remain normal?

Again much appriciated.

NBVC · #5 06-24-2009, 11:27 AM

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

Bartholemu · #6 06-25-2009, 04:17 AM

Ahh yes indeed you are right !! thanks ever so much guys very helpfull !!!

Bartholemu · #7 08-14-2009, 06:08 AM

hello again wise ppl,

am affraid i am in need of assistance again, i tried working it out for myself but still no result

the last formula you gave me worked perfect, now i need it to do the following:

R1 R2 R3 R4 R5 R6 R7 R8 R9 R10 R11 R12
0 1 1 35 55 87 14 0 69 87 11 49

i m trying to make the formula highlight the 2 lowest values in the above string, so in the above example the 0 and only one of the 1s.

i tried the following but i keep getting syntax errors... any suggestions ?

=AND(F3=SMALL($F3:$K3,1),($F3:$K3,2),COUNTIF($F3:F3,F3)=1)

thanks

DonkeyOte · #8 08-14-2009, 06:14 AM

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:

Code:

=AND(F3<=SMALL($F3:$Q3,2),COUNTIF($F3:F3,"<="&F3)<=2)

Bartholemu · #9 08-14-2009, 06:30 AM

yes sorry my mistake i didn t notice the second 0 it should have read:

R1 R2 R3 R4 R5 R6 R7 R8 R9 R10 R11 R12
0 1 1 35 55 87 14 1 69 87 11 49

does the above formula stay the same?

DonkeyOte · #10 08-14-2009, 06:40 AM

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

Bartholemu · #11 08-14-2009, 06:52 AM

great it works thanks a lot!! does that mean that when i want the 3 smallest i can just change it to:

=AND(F3<=SMALL($F3:$Q3,3),COUNTIF($F3:F3,"<="&F3)<=3) ??

DonkeyOte · #12 08-14-2009, 06:53 AM

Yes, that is correct.

Welcome to the Excel Forum

Forum Statistics