Select middle value within a range

[ptlagow]

I have a listing with three columns. i would like to select the middle value, not necessarily the median, of the three. If there are less than 3 values select the lowest value.

Set up like this:

Credit Scores Selection
Vendor 1 Vendor 2 Vendor 3
718 756 745 745
650 650 660 650
625 612 612

Any help would be appreciated.
Right now I created the following, but there has got to be something easier.

=IF(B4=C4,B4,IF(B4=D4,B4,IF(C4=D4,C4,IF(OR(AND(B4>C4,B4<D4),
AND(B4>D4,B4<C4)),B4,IF(OR(AND(C4>B4,C4<D4),AND(C4>D4,C4<B4)),C4,D4)))))

 

[AnotherHiggins]

Median would work for the examples you gave.

Try:
[COLOR=blue white]=if(count(A2:C2)<3,min(A2:C2),median(A2:C2))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[mintjulep]

I suggest something with Max and Min functions.

 

[strongm]

Here's a starter: look up the SMALL (or LARGE) worksheet function

 

[AnotherHiggins]

OK, maybe I'm being dense today, but what exactly do you mean by, "i would like to select the middle value, not necessarily the median, of the three"?

The median of three values will always be the 'middle' number. In your second example (650, 650 and 660), 650 will be returned as the median. And you indicate that you want it to return 650.

Am I missing something?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[strongm]

Quite right. Median works fine with 3 numbers; I got distracted from this fact by the OP stating they didn't want the median.

 

[ptlagow]

Sorry I did misspeak, and the median formula listed
=if(count(A2:C2)<3,min(A2:C2),median(A2:C2))
works perfectly!

Thanks to all