Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return 1st, 2nd, 3rd largest test values
I am trying to write a formula that will look at a column of data and return
the 1st, 2nd, and 3rd largest values. My major issue is that the values are text. Example: Term Reasons Failed Testing Failed Testing Failed Tesing Fingerprinting Failed BG Check Failed BG Check I need to return the data as... #1 Term Reason = Failed Testing #2 Term Reason = Failed BG Check #3 Term Reason = Fingerprinting Using Excel 2003 Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return 1st, 2nd, 3rd largest test values
rng1 is a defined name range
B2: =INDEX(rng1,MODE(MATCH(rng1,rng1,0)+{0,0})) B3: =INDEX(rng1,MODE(IF(COUNTIF($B$2:B2,rng1)=0,MATCH( rng1,rng1,0)+{0,0}))) Both formale are required ctrl+shift+enter, not just enter copy from B3 down "John" wrote: I am trying to write a formula that will look at a column of data and return the 1st, 2nd, and 3rd largest values. My major issue is that the values are text. Example: Term Reasons Failed Testing Failed Testing Failed Tesing Fingerprinting Failed BG Check Failed BG Check I need to return the data as... #1 Term Reason = Failed Testing #2 Term Reason = Failed BG Check #3 Term Reason = Fingerprinting Using Excel 2003 Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return 1st, 2nd, 3rd largest test values
One way...
Assume data is in the range A2:A7 and there are no empty cells within the range. Enter this formula in C2: =INDEX(A2:A7,MODE(MATCH(A2:A7,A2:A7,0)+{0,0})) Enter this array formula** in C3 and copy down to C4: =INDEX(A$2:A$7,MODE(IF(COUNTIF(C$2:C2,A$2:A$7)=0,M ATCH(A$2:A$7,A$2:A$7,0)+{0,0}))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "John" wrote in message ... I am trying to write a formula that will look at a column of data and return the 1st, 2nd, and 3rd largest values. My major issue is that the values are text. Example: Term Reasons Failed Testing Failed Testing Failed Tesing Fingerprinting Failed BG Check Failed BG Check I need to return the data as... #1 Term Reason = Failed Testing #2 Term Reason = Failed BG Check #3 Term Reason = Fingerprinting Using Excel 2003 Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return 1st, 2nd, 3rd largest test values
Not having much luck. Here is what I am entering.
=INDEX(Data!M2:M1000,MODE(MATCH(Data!M2:M1000,Data !M2:M1000,0)+{0,0})) I'm setting it as an array and all I get is #N/A What am I doing wrong? "T. Valko" wrote: One way... Assume data is in the range A2:A7 and there are no empty cells within the range. Enter this formula in C2: =INDEX(A2:A7,MODE(MATCH(A2:A7,A2:A7,0)+{0,0})) Enter this array formula** in C3 and copy down to C4: =INDEX(A$2:A$7,MODE(IF(COUNTIF(C$2:C2,A$2:A$7)=0,M ATCH(A$2:A$7,A$2:A$7,0)+{0,0}))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "John" wrote in message ... I am trying to write a formula that will look at a column of data and return the 1st, 2nd, and 3rd largest values. My major issue is that the values are text. Example: Term Reasons Failed Testing Failed Testing Failed Tesing Fingerprinting Failed BG Check Failed BG Check I need to return the data as... #1 Term Reason = Failed Testing #2 Term Reason = Failed BG Check #3 Term Reason = Fingerprinting Using Excel 2003 Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return 1st, 2nd, 3rd largest test values
Here's a small sample file that demonstrates this.
xTextMode.xls 14kb http://cjoint.com/?dvw31x1fJz The file shows 2 different methods. One in which there are no empty cells within the range and one where the range does have empty cells. -- Biff Microsoft Excel MVP "John" wrote in message ... Not having much luck. Here is what I am entering. =INDEX(Data!M2:M1000,MODE(MATCH(Data!M2:M1000,Data !M2:M1000,0)+{0,0})) I'm setting it as an array and all I get is #N/A What am I doing wrong? "T. Valko" wrote: One way... Assume data is in the range A2:A7 and there are no empty cells within the range. Enter this formula in C2: =INDEX(A2:A7,MODE(MATCH(A2:A7,A2:A7,0)+{0,0})) Enter this array formula** in C3 and copy down to C4: =INDEX(A$2:A$7,MODE(IF(COUNTIF(C$2:C2,A$2:A$7)=0,M ATCH(A$2:A$7,A$2:A$7,0)+{0,0}))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "John" wrote in message ... I am trying to write a formula that will look at a column of data and return the 1st, 2nd, and 3rd largest values. My major issue is that the values are text. Example: Term Reasons Failed Testing Failed Testing Failed Tesing Fingerprinting Failed BG Check Failed BG Check I need to return the data as... #1 Term Reason = Failed Testing #2 Term Reason = Failed BG Check #3 Term Reason = Fingerprinting Using Excel 2003 Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return 1st, 2nd, 3rd largest test values
Neat formulae. I can figure out what they're doing up to +{0,0}. Can you
explain what this is doing? TIA "Teethless mama" wrote: rng1 is a defined name range B2: =INDEX(rng1,MODE(MATCH(rng1,rng1,0)+{0,0})) B3: =INDEX(rng1,MODE(IF(COUNTIF($B$2:B2,rng1)=0,MATCH( rng1,rng1,0)+{0,0}))) Both formale are required ctrl+shift+enter, not just enter copy from B3 down "John" wrote: I am trying to write a formula that will look at a column of data and return the 1st, 2nd, and 3rd largest values. My major issue is that the values are text. Example: Term Reasons Failed Testing Failed Testing Failed Tesing Fingerprinting Failed BG Check Failed BG Check I need to return the data as... #1 Term Reason = Failed Testing #2 Term Reason = Failed BG Check #3 Term Reason = Fingerprinting Using Excel 2003 Thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return 1st, 2nd, 3rd largest test values
Per Excel Help:
"If the data set contains no duplicate data points, MODE returns the #N/A error value" So...the +{0,0} causes all values to be duplicated. Consequently, any item that appears only once in the list would have 2 values for MODE to evaluate: Example: If the results of the match function we {1;1;1;4;4;4;4;8} Then {1;1;1;4;4;4;4;8}+{0,0} results in: {1,1;1,1;1,1;4,4;4,4;4,4;4,4;8,8} Notice how the single 8 becomes two 8's. Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "alstubna" wrote in message ... Neat formulae. I can figure out what they're doing up to +{0,0}. Can you explain what this is doing? TIA "Teethless mama" wrote: rng1 is a defined name range B2: =INDEX(rng1,MODE(MATCH(rng1,rng1,0)+{0,0})) B3: =INDEX(rng1,MODE(IF(COUNTIF($B$2:B2,rng1)=0,MATCH( rng1,rng1,0)+{0,0}))) Both formale are required ctrl+shift+enter, not just enter copy from B3 down "John" wrote: I am trying to write a formula that will look at a column of data and return the 1st, 2nd, and 3rd largest values. My major issue is that the values are text. Example: Term Reasons Failed Testing Failed Testing Failed Tesing Fingerprinting Failed BG Check Failed BG Check I need to return the data as... #1 Term Reason = Failed Testing #2 Term Reason = Failed BG Check #3 Term Reason = Fingerprinting Using Excel 2003 Thanks! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return 1st, 2nd, 3rd largest test values
Yep, now we're rolln'!
Thanks! "T. Valko" wrote: Here's a small sample file that demonstrates this. xTextMode.xls 14kb http://cjoint.com/?dvw31x1fJz The file shows 2 different methods. One in which there are no empty cells within the range and one where the range does have empty cells. -- Biff Microsoft Excel MVP "John" wrote in message ... Not having much luck. Here is what I am entering. =INDEX(Data!M2:M1000,MODE(MATCH(Data!M2:M1000,Data !M2:M1000,0)+{0,0})) I'm setting it as an array and all I get is #N/A What am I doing wrong? "T. Valko" wrote: One way... Assume data is in the range A2:A7 and there are no empty cells within the range. Enter this formula in C2: =INDEX(A2:A7,MODE(MATCH(A2:A7,A2:A7,0)+{0,0})) Enter this array formula** in C3 and copy down to C4: =INDEX(A$2:A$7,MODE(IF(COUNTIF(C$2:C2,A$2:A$7)=0,M ATCH(A$2:A$7,A$2:A$7,0)+{0,0}))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "John" wrote in message ... I am trying to write a formula that will look at a column of data and return the 1st, 2nd, and 3rd largest values. My major issue is that the values are text. Example: Term Reasons Failed Testing Failed Testing Failed Tesing Fingerprinting Failed BG Check Failed BG Check I need to return the data as... #1 Term Reason = Failed Testing #2 Term Reason = Failed BG Check #3 Term Reason = Fingerprinting Using Excel 2003 Thanks! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return 1st, 2nd, 3rd largest test values
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "John" wrote in message ... Yep, now we're rolln'! Thanks! "T. Valko" wrote: Here's a small sample file that demonstrates this. xTextMode.xls 14kb http://cjoint.com/?dvw31x1fJz The file shows 2 different methods. One in which there are no empty cells within the range and one where the range does have empty cells. -- Biff Microsoft Excel MVP "John" wrote in message ... Not having much luck. Here is what I am entering. =INDEX(Data!M2:M1000,MODE(MATCH(Data!M2:M1000,Data !M2:M1000,0)+{0,0})) I'm setting it as an array and all I get is #N/A What am I doing wrong? "T. Valko" wrote: One way... Assume data is in the range A2:A7 and there are no empty cells within the range. Enter this formula in C2: =INDEX(A2:A7,MODE(MATCH(A2:A7,A2:A7,0)+{0,0})) Enter this array formula** in C3 and copy down to C4: =INDEX(A$2:A$7,MODE(IF(COUNTIF(C$2:C2,A$2:A$7)=0,M ATCH(A$2:A$7,A$2:A$7,0)+{0,0}))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "John" wrote in message ... I am trying to write a formula that will look at a column of data and return the 1st, 2nd, and 3rd largest values. My major issue is that the values are text. Example: Term Reasons Failed Testing Failed Testing Failed Tesing Fingerprinting Failed BG Check Failed BG Check I need to return the data as... #1 Term Reason = Failed Testing #2 Term Reason = Failed BG Check #3 Term Reason = Fingerprinting Using Excel 2003 Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look for similar text and find the largest value and return value | Excel Discussion (Misc queries) | |||
find largest values, then return corresponding row values. | Excel Discussion (Misc queries) | |||
Find Largest and Return Column Name | Excel Worksheet Functions | |||
return largest number of a list of numbers in the same cell | Excel Worksheet Functions | |||
How do I return the cell address of the largest of a set of values | Excel Discussion (Misc queries) |