Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function select second lowest
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function select second lowest
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(B1:D1,IF(COUNTA(B1:D1)<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(B1:D1)0, LARGE(B1:D1,IF(COUNTA(B1:D1)<2,1,2)), 0) -- Hope that helps. Vergel Adriano "Arne Hegefors" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function select second lowest
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(B1:D1,"0")=1,SUM(B1:D1),IF(COUNTIF(B1 :D1,"0")=2,MIN(B1:D1),IF(COUNTIF(B1:D1,"0")=3,ME DIAN(B1:D1),"Unspecified"))) I've assumed cols B,C & D for your numbers "Arne Hegefors" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function select second lowest
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: 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(B1:D1,IF(COUNTA(B1:D1)<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(B1:D1)0, LARGE(B1:D1,IF(COUNTA(B1:D1)<2,1,2)), 0) -- Hope that helps. Vergel Adriano "Arne Hegefors" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function select second lowest
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) -- Hope that helps. Vergel Adriano "Arne Hegefors" wrote: 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: 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(B1:D1,IF(COUNTA(B1:D1)<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(B1:D1)0, LARGE(B1:D1,IF(COUNTA(B1:D1)<2,1,2)), 0) -- Hope that helps. Vergel Adriano "Arne Hegefors" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare 2 columns and select the lowest | Excel Discussion (Misc queries) | |||
formula to select lowest value | Excel Discussion (Misc queries) | |||
Select lowest value from 3 columns | Excel Worksheet Functions | |||
determine lowest number in several columns and replace lowest numb | Excel Worksheet Functions | |||
Function that gives the SUM minus 4 lowest numbers | Excel Programming |