| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format?
Excel2003
I have a list of values (approx 30) of which some may repeat. I wish to set cell pattern of "next to lowest" value. =MIN(myrange) will set cell pattern containing lowest value, but I need to be 1 click up from MIN value ... (I need to set cell pattern of "next to lowest" value(s)). Solutions? ... Thanks ... Kha |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Format?
Look at the SMALL worksheet function.
-- Jim "Ken" wrote in message ... | Excel2003 | | I have a list of values (approx 30) of which some may repeat. | | I wish to set cell pattern of "next to lowest" value. | | =MIN(myrange) will set cell pattern containing lowest value, but I need to | be 1 click up from MIN value ... (I need to set cell pattern of "next to | lowest" value(s)). | | Solutions? ... Thanks ... Kha | | |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Format?
Use the SMALL function, with a parameter of 2 to get the second
smallest - XL Help describes it well. Hope this helps. Pete On Apr 9, 1:01*pm, Ken wrote: Excel2003 I have a list of values (approx 30) of which some may repeat. I wish to set cell pattern of "next to lowest" value. =MIN(myrange) will set cell pattern containing lowest value, but I need to be 1 click up from MIN value ... (I need to set cell pattern of "next to lowest" value(s)). Solutions? ... Thanks ... Kha |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Format?
Issue with SMALL Function is ... when MIN Value randomly Repeats SMALL
Function returns MIN Value. Thanks ... Kha "Pete_UK" wrote: Use the SMALL function, with a parameter of 2 to get the second smallest - XL Help describes it well. Hope this helps. Pete On Apr 9, 1:01 pm, Ken wrote: Excel2003 I have a list of values (approx 30) of which some may repeat. I wish to set cell pattern of "next to lowest" value. =MIN(myrange) will set cell pattern containing lowest value, but I need to be 1 click up from MIN value ... (I need to set cell pattern of "next to lowest" value(s)). Solutions? ... Thanks ... Kha |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Format?
Well, if you have 3 values that happen to be the minumum, those are
the 3 smallest values - you will find the next largest value with a parameter of 4, so instead of using a specific number you can use COUNTIF for the smallest number and then add 1 onto it, something like this: =SMALL(A1:A10,COUNTIF(A1:A10,SMALL(A1:A10,1))+1) This will find the second smallest numerical value in A1:A10, even if you have several that are equal to the minimum. Hope this helps. Pete On Apr 10, 3:12*pm, Ken wrote: Issue with SMALL Function is ... when MIN Value randomly Repeats SMALL Function returns MIN Value. Thanks ... Kha "Pete_UK" wrote: Use the SMALL function, with a parameter of 2 to get the second smallest - XL Help describes it well. Hope this helps. Pete On Apr 9, 1:01 pm, Ken wrote: Excel2003 I have a list of values (approx 30) of which some may repeat. I wish to set cell pattern of "next to lowest" value. =MIN(myrange) will set cell pattern containing lowest value, but I need to be 1 click up from MIN value ... (I need to set cell pattern of "next to lowest" value(s)). Solutions? ... Thanks ... Kha- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Format?
Pete ... (Hi)
SMALL ... Finds small value COUNTIF(SMALL)+1 ... becomes the SMALL "k" position Wrap all this in SMALL again ... Right Answer. I have no idea how the members of these boards that are intimate with Excel unravel all these spider webs presented on a daily basis ... BUT I am grateful that you do ... Another one bites the dust ... Thanks ... Kha "Pete_UK" wrote: Well, if you have 3 values that happen to be the minumum, those are the 3 smallest values - you will find the next largest value with a parameter of 4, so instead of using a specific number you can use COUNTIF for the smallest number and then add 1 onto it, something like this: =SMALL(A1:A10,COUNTIF(A1:A10,SMALL(A1:A10,1))+1) This will find the second smallest numerical value in A1:A10, even if you have several that are equal to the minimum. Hope this helps. Pete On Apr 10, 3:12 pm, Ken wrote: Issue with SMALL Function is ... when MIN Value randomly Repeats SMALL Function returns MIN Value. Thanks ... Kha "Pete_UK" wrote: Use the SMALL function, with a parameter of 2 to get the second smallest - XL Help describes it well. Hope this helps. Pete On Apr 9, 1:01 pm, Ken wrote: Excel2003 I have a list of values (approx 30) of which some may repeat. I wish to set cell pattern of "next to lowest" value. =MIN(myrange) will set cell pattern containing lowest value, but I need to be 1 click up from MIN value ... (I need to set cell pattern of "next to lowest" value(s)). Solutions? ... Thanks ... Kha- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Format?
Thanks for feeding back, Ken - I'm glad to be of help.
Of course, in your specific case you could have used MIN(A1:A10) instead of the inner SMALL function, but then you might have come back asking to find the third smallest value ... Hopefully you can see how you might do that now. Pete "Ken" wrote in message ... Pete ... (Hi) SMALL ... Finds small value COUNTIF(SMALL)+1 ... becomes the SMALL "k" position Wrap all this in SMALL again ... Right Answer. I have no idea how the members of these boards that are intimate with Excel unravel all these spider webs presented on a daily basis ... BUT I am grateful that you do ... Another one bites the dust ... Thanks ... Kha "Pete_UK" wrote: Well, if you have 3 values that happen to be the minumum, those are the 3 smallest values - you will find the next largest value with a parameter of 4, so instead of using a specific number you can use COUNTIF for the smallest number and then add 1 onto it, something like this: =SMALL(A1:A10,COUNTIF(A1:A10,SMALL(A1:A10,1))+1) This will find the second smallest numerical value in A1:A10, even if you have several that are equal to the minimum. Hope this helps. Pete On Apr 10, 3:12 pm, Ken wrote: Issue with SMALL Function is ... when MIN Value randomly Repeats SMALL Function returns MIN Value. Thanks ... Kha "Pete_UK" wrote: Use the SMALL function, with a parameter of 2 to get the second smallest - XL Help describes it well. Hope this helps. Pete On Apr 9, 1:01 pm, Ken wrote: Excel2003 I have a list of values (approx 30) of which some may repeat. I wish to set cell pattern of "next to lowest" value. =MIN(myrange) will set cell pattern containing lowest value, but I need to be 1 click up from MIN value ... (I need to set cell pattern of "next to lowest" value(s)). Solutions? ... Thanks ... Kha- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
| How to create a conditional format that changes the number format | Excel Discussion (Misc queries) | |||
| Conditional Format - Format Transfer To Chart | Excel Discussion (Misc queries) | |||
| copy conditional format to regular format | Setting up and Configuration of Excel | |||
| copy conditional format as ACTUAL format | Excel Discussion (Misc queries) | |||
Hybrid Mode
