Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Smallest and Largest.
I have a column of random numbers and need to find the smallest, second
smallest, third smallest, third largest, second largest, largest and highlight these numbers with different font colors. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Smallest and Largest.
You might try looking in the help index for SMALL
-- Don Guillett Microsoft MVP Excel SalesAid Software "dlbeiler" wrote in message ... I have a column of random numbers and need to find the smallest, second smallest, third smallest, third largest, second largest, largest and highlight these numbers with different font colors. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Smallest and Largest.
Use conditional formatting. Unfortunately if you have Excel 2003 or
earlier, you can only set 3 rules, meaning 3 different colors. Select the entire range, activate first cell. My example is a list of random numbers in cells A1:A42. I select the entire range, and activate A1. Then, in conditional formatting dialog box use "Formula is" and type in this: =A1=SMALL($A$1:$A$42,1) This will return TRUE for the smallest number in range. Select Format, and choose a different font color. For second smallest number, use this "Formula is" rule: =A1=SMALL($A$1:$A$42,2) The opposite of this is the LARGE() function. Largest number in range will be found like this: =A1=LARGE($A$1:$A$42,1) Once you've made up the rules, recalc a few times to ensure you are getting the correct behavior. On Oct 9, 2:48 pm, dlbeiler wrote: I have a column of random numbers and need to find the smallest, second smallest, third smallest, third largest, second largest, largest and highlight these numbers with different font colors. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Smallest and Largest.
Thank you for your response. I am using Excel 2000 and am limited to 3
rules. In that case, would it be possible to combine the smallest, second smallest and third smallest into one rule and thus use the same font color to identify all three? "ilia" wrote: Use conditional formatting. Unfortunately if you have Excel 2003 or earlier, you can only set 3 rules, meaning 3 different colors. Select the entire range, activate first cell. My example is a list of random numbers in cells A1:A42. I select the entire range, and activate A1. Then, in conditional formatting dialog box use "Formula is" and type in this: =A1=SMALL($A$1:$A$42,1) This will return TRUE for the smallest number in range. Select Format, and choose a different font color. For second smallest number, use this "Formula is" rule: =A1=SMALL($A$1:$A$42,2) The opposite of this is the LARGE() function. Largest number in range will be found like this: =A1=LARGE($A$1:$A$42,1) Once you've made up the rules, recalc a few times to ensure you are getting the correct behavior. On Oct 9, 2:48 pm, dlbeiler wrote: I have a column of random numbers and need to find the smallest, second smallest, third smallest, third largest, second largest, largest and highlight these numbers with different font colors. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Smallest and Largest.
Try this idea for condition 1. Add small,2 in the middle.
=OR(O3=SMALL($O$2:$O$8,1),O2=SMALL($O2:$O8,3)) -- Don Guillett Microsoft MVP Excel SalesAid Software "dlbeiler" wrote in message ... I have a column of random numbers and need to find the smallest, second smallest, third smallest, third largest, second largest, largest and highlight these numbers with different font colors. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
functions largest to smallest | Excel Worksheet Functions | |||
Can I calculate a statistical range (largest minus smallest)? | Excel Discussion (Misc queries) | |||
COMPARE DATA AND LIST SMALLEST AND LARGEST ENTRY BY UNIT | Excel Discussion (Misc queries) | |||
Getting the 2nd largest or smallest valuesin a range | Excel Discussion (Misc queries) | |||
Ranking cells largest to smallest | Excel Discussion (Misc queries) |