Function select second lowest

[Guest]

Hi! I have a long list with names of banks. I also have three different
numbers (between 1 and 10) for every bank. Eg Bank A 5 7 4, Bank B 5 9 3.
Sometimes there are not three numbers for a bank but only one or two. There
are never more than three numbers though. I want to have a forth column. This
column shall give me a new number based on the other numbers. The sorting
shall work like this. If a bank only has one number then that number shall be
presented in the forth coulmn. If there are two numbers then the lowest one
shall be used. If there are three numbers then the two highest numbers shall
be chosen and if they are different the lowest one of the two highest shall
be presented in the forth coumn. I do not know if you can do this using
normal worksheet function of if a user defined function is necessary. PLease
any help very much appreciated! Thanks alot!

 

[Guest]

Somebody else might have a better idea but here's one way to do it with
formulas. Assuming the numbers are in columns B to D, in E1, put this
formula then copy down as needed.

=LARGE(B11,IF(COUNTA(B11)<2,1,2))

There must be at least one number in the 3 cells.. If all 3 cells are
blanks, you get #NUM!. If there's a possibility of all 3 cells being blank,
this formula will show a 0 on the fourth column to avoid getting the #NUM!.

=IF(COUNTA(B11)>0, LARGE(B11,IF(COUNTA(B11)<2,1,2)), 0)

 

[Guest]

This might be a bit long-winded but it works without resorting to a function:-

In your 4th cell type this and drag down:-

=IF(COUNTIF(B11,">0")=1,SUM(B11),IF(COUNTIF(B11,">0")=2,MIN(B11),IF(COUNTIF(B11,">0")=3,MEDIAN(B11),"Unspecified")))

I've assumed cols B,C & D for your numbers

 

[Guest]

Thanks alot! The only problem i have now is that the three columns are not
placed next to each other (i cannot chnage that) so the LARGE function does
not work. I have tried using "+" but i cannot get it to work. is it possible
to solve this? thanks alot!

"Vergel Adriano" skrev:

 

[Guest]

maybe a UDF?

Public Function Get2ndHighest(cNo1 As Range, cNo2 As Range, cNo3 As Range)
Dim arrNo(2) As Integer
Dim count As Integer

Get2ndHighest = 0

arrNo(0) = cNo1.Value
arrNo(1) = cNo2.Value
arrNo(2) = cNo3.Value

count = WorksheetFunction.CountA(cNo1, cNo2, cNo3)
If count > 0 Then
count = IIf(count < 2, 1, 2)
Get2ndHighest = WorksheetFunction.Large(arrNo, count)
End If

End Function

Assuming the numbers are in B1, D1, and F1; in G1, enter:

=Get2ndHighest(B1, D1, F1)