Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function in Excel
I have a large spreadsheet, and I am attempting to aggregate data contained
in this spreadsheet. Here is what I am trying to do: I have labels across the top row of the spreasheet. The spreadsheet is set up like this: ABCDEFG 1 11345 2 12145 3 12341 What I'd like to do is determine for each row, what the minimum value is, the number of instances the minimum value is achieved, and the corresponding value for the first row ON THE LAST OCCASION OF THE MINIMUM VALUE going from left to right. FOr example, if the function I am trying to use were entered correctly, it would return (for Row 1) a minimum value of 1 that is recorded on two instances AND the final instance occurs at Column B (top row of values). Similarly for row 3, my function would return a minimum value of 1 that is recorded on two instances AND the final instance occurs at Column E (note that in my example above, things might not line up). In any event, any help would be greatly appreciated. Many thanks -- Anthony |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function in Excel
On Tue, 3 Feb 2009 10:14:01 -0800, Anthony
wrote: I have a large spreadsheet, and I am attempting to aggregate data contained in this spreadsheet. Here is what I am trying to do: I have labels across the top row of the spreasheet. The spreadsheet is set up like this: ABCDEFG 1 11345 2 12145 3 12341 What I'd like to do is determine for each row, what the minimum value is, the number of instances the minimum value is achieved, and the corresponding value for the first row ON THE LAST OCCASION OF THE MINIMUM VALUE going from left to right. FOr example, if the function I am trying to use were entered correctly, it would return (for Row 1) a minimum value of 1 that is recorded on two instances AND the final instance occurs at Column B (top row of values). Similarly for row 3, my function would return a minimum value of 1 that is recorded on two instances AND the final instance occurs at Column E (note that in my example above, things might not line up). In any event, any help would be greatly appreciated. Many thanks Try the following formula ="("&MIN(A1:G1)&","&COUNTIF(A1:G1,MIN(A1:G1))&","& CHAR(64+MAX(COLUMN(A1:G1)*(A1:G1=MIN(A1:G1))))&")" Note: This is an array formula that should be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Change the range A1:G1 to fit your data range. The result for you sample data will be "(1,2,B)" for row "(1,2,C)" for row 2 "(1,2,E)" for row 3 Maybe you should consider not to have all three results in the same formula but split it into three different formulas. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function in Excel
I already have the MIN function and the COUNT function allowing me to get the
minimum value and # of instances the value occurs. It is the 3rd task I am attempting to incorporate, that is match the last instance of the minumum value with the corresponding value in the top row abov it. How would I do this? Again, many thanks! -- Anthony "Lars-Ã…ke Aspelin" wrote: On Tue, 3 Feb 2009 10:14:01 -0800, Anthony wrote: I have a large spreadsheet, and I am attempting to aggregate data contained in this spreadsheet. Here is what I am trying to do: I have labels across the top row of the spreasheet. The spreadsheet is set up like this: ABCDEFG 1 11345 2 12145 3 12341 What I'd like to do is determine for each row, what the minimum value is, the number of instances the minimum value is achieved, and the corresponding value for the first row ON THE LAST OCCASION OF THE MINIMUM VALUE going from left to right. FOr example, if the function I am trying to use were entered correctly, it would return (for Row 1) a minimum value of 1 that is recorded on two instances AND the final instance occurs at Column B (top row of values). Similarly for row 3, my function would return a minimum value of 1 that is recorded on two instances AND the final instance occurs at Column E (note that in my example above, things might not line up). In any event, any help would be greatly appreciated. Many thanks Try the following formula ="("&MIN(A1:G1)&","&COUNTIF(A1:G1,MIN(A1:G1))&","& CHAR(64+MAX(COLUMN(A1:G1)*(A1:G1=MIN(A1:G1))))&")" Note: This is an array formula that should be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Change the range A1:G1 to fit your data range. The result for you sample data will be "(1,2,B)" for row "(1,2,C)" for row 2 "(1,2,E)" for row 3 Maybe you should consider not to have all three results in the same formula but split it into three different formulas. Hope this helps / Lars-Ã…ke |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function in Excel
On Tue, 3 Feb 2009 11:00:01 -0800, Anthony
wrote: I already have the MIN function and the COUNT function allowing me to get the minimum value and # of instances the value occurs. It is the 3rd task I am attempting to incorporate, that is match the last instance of the minumum value with the corresponding value in the top row abov it. How would I do this? Again, many thanks! Please define "the top row above it". Is "the top row" row number 1? Or is it the row just above? In your example, is the A, B, C, D, E, F, and G data or is it just the normal column headers? Lars-Åke |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function in Excel
Thank you. Sorry for lack of clarification. In my example, it is the
A,B,C,D, etc...not the header. -- Anthony "Lars-Ã…ke Aspelin" wrote: On Tue, 3 Feb 2009 11:00:01 -0800, Anthony wrote: I already have the MIN function and the COUNT function allowing me to get the minimum value and # of instances the value occurs. It is the 3rd task I am attempting to incorporate, that is match the last instance of the minumum value with the corresponding value in the top row abov it. How would I do this? Again, many thanks! Please define "the top row above it". Is "the top row" row number 1? Or is it the row just above? In your example, is the A, B, C, D, E, F, and G data or is it just the normal column headers? Lars-Ã…ke |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function in Excel
On Tue, 3 Feb 2009 11:58:03 -0800, Anthony
wrote: Thank you. Sorry for lack of clarification. In my example, it is the A,B,C,D, etc...not the header. Does that mean that 1,2,3 to the left also are just data and not the row numbers? Maybe better to use some other data in the example... So, if this is your table Alfa Bravo Charlie Delta Echo data1 1 1 3 4 5 data2 1 2 1 4 5 data3 1 2 3 4 1 try the following formula =INDEX(A$1:F$1,MAX(COLUMN(B:F)*(B2:F2=MIN(B2:F2))) ) For row2, with "data1" it will return "Bravo" For row3 it will return "Charlie" For row4 it will return "Echo" Note: The formula is an array formula that should be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Lars-Åke |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function in Excel
After some tinkering, this worked VERY well. Thank you VERY much. This
saved me hours of work. Regards. -- Anthony "Lars-Ã…ke Aspelin" wrote: On Tue, 3 Feb 2009 11:58:03 -0800, Anthony wrote: Thank you. Sorry for lack of clarification. In my example, it is the A,B,C,D, etc...not the header. Does that mean that 1,2,3 to the left also are just data and not the row numbers? Maybe better to use some other data in the example... So, if this is your table Alfa Bravo Charlie Delta Echo data1 1 1 3 4 5 data2 1 2 1 4 5 data3 1 2 3 4 1 try the following formula =INDEX(A$1:F$1,MAX(COLUMN(B:F)*(B2:F2=MIN(B2:F2))) ) For row2, with "data1" it will return "Bravo" For row3 it will return "Charlie" For row4 it will return "Echo" Note: The formula is an array formula that should be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Lars-Ã…ke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Lookup function | Excel Worksheet Functions | |||
Help with Excel lookup function please | Excel Discussion (Misc queries) | |||
advanced use of the Excel lookup function | Excel Discussion (Misc queries) | |||
Lookup function in Excel | Excel Worksheet Functions | |||
IF/ LOOKUP FUNCTION - Excel 2000 | Excel Worksheet Functions |