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.
Last edited by NBVC; 06-25-2009 at 09:00 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
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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.
Did you try DonkeyOte's formula? I think that is exactly what it does.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Ahh yes indeed you are right !! thanks ever so much guys very helpfull !!!
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
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)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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?
yes, it will highlight the lowest 2 values in order - once 2 values are found no others will be formatted.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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) ??
Yes, that is correct.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks