Conditional Format?

[Ken]

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

 

[Jim Rech]

Look at the SMALL worksheet function.

--
Jim
| 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
|
|

 

[Pete_UK]

Use the SMALL function, with a parameter of 2 to get the second
smallest - XL Help describes it well.

Hope this helps.

Pete

 

[Ken]

Issue with SMALL Function is ... when MIN Value randomly Repeats SMALL
Function returns MIN Value.

Thanks ... Kha

 

[Pete_UK]

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

 

[Ken]

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]

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