| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Top values with changing criteria
I am trying to return the top N values from a list with corresponding
names. The criteria for ranking changes. ColA:C contain my data (thousands of rows). ColD:E contain my criteria (note below I want to return top 3 values). ColF sorts in Top3 order. ColA ColB ColC ColD ColE ColF Movie Friday $25 Movie Friday $26 Movie Friday $26 Movie Friday $25 Movie Friday $18 Movie Friday $18 TV Tues $12 Movie Tues $21 TV Tues $19 Movie Tues $19 TV Tues $21 Movie Tues $12 I would like to use a formla that can rank the data when the two criteria in ColD & E are met. So when Movie and Friday show up in the list of thousands, just return the top 3 values. Then when Movie and Tues are met, return the top 3 values. I know this can be done, but I am having trouble fixing my formulas when the two criteria change. Thanks in advance for your help. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Top values with changing criteria
Would this work out for you?
Duplicates are ranked as individual values: ColA criteria in D1, ColB criteria in E1, Enter formula in F1 and copy down as many rows as the rankings you wish to return. =SUMPRODUCT(LARGE((A$2:A$13=D$1)*(B$2:B$13=E$1)*C$ 2:C$13,ROWS($1:1))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... I am trying to return the top N values from a list with corresponding names. The criteria for ranking changes. ColA:C contain my data (thousands of rows). ColD:E contain my criteria (note below I want to return top 3 values). ColF sorts in Top3 order. ColA ColB ColC ColD ColE ColF Movie Friday $25 Movie Friday $26 Movie Friday $26 Movie Friday $25 Movie Friday $18 Movie Friday $18 TV Tues $12 Movie Tues $21 TV Tues $19 Movie Tues $19 TV Tues $21 Movie Tues $12 I would like to use a formla that can rank the data when the two criteria in ColD & E are met. So when Movie and Friday show up in the list of thousands, just return the top 3 values. Then when Movie and Tues are met, return the top 3 values. I know this can be done, but I am having trouble fixing my formulas when the two criteria change. Thanks in advance for your help. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Top values with changing criteria
Hi,
You can try this array formula (Ctrl+Shift+Enter) in cell E18. Copy this formual down =LARGE(($C$3:$C$14=C$18)*($D$3:$D$14=D$18)*($E$3:$ E$14),ROWS(C$18:C18)) I have assumed that the data is in range C3:E14. Movie is in cell C18 and Friday is in cell D18 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com wrote in message ... I am trying to return the top N values from a list with corresponding names. The criteria for ranking changes. ColA:C contain my data (thousands of rows). ColD:E contain my criteria (note below I want to return top 3 values). ColF sorts in Top3 order. ColA ColB ColC ColD ColE ColF Movie Friday $25 Movie Friday $26 Movie Friday $26 Movie Friday $25 Movie Friday $18 Movie Friday $18 TV Tues $12 Movie Tues $21 TV Tues $19 Movie Tues $19 TV Tues $21 Movie Tues $12 I would like to use a formla that can rank the data when the two criteria in ColD & E are met. So when Movie and Friday show up in the list of thousands, just return the top 3 values. Then when Movie and Tues are met, return the top 3 values. I know this can be done, but I am having trouble fixing my formulas when the two criteria change. Thanks in advance for your help. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Top values with changing criteria
On May 15, 5:46*pm, "RagDyer" wrote:
Would this work out for you? Duplicates are ranked as individual values: ColA criteria in D1, ColB criteria in E1, Enter formula in F1 and copy down as many rows as the rankings you wish to return. =SUMPRODUCT(LARGE((A$2:A$13=D$1)*(B$2:B$13=E$1)*C$ 2:C$13,ROWS($1:1))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... I am trying to return the top N values from a list with corresponding names. *The criteria for ranking changes. ColA:C contain my data (thousands of rows). *ColD:E contain my criteria (note below I want to return top 3 values). *ColF sorts in Top3 order. ColA * * * * * * *ColB * * * * * * *ColC ColD * * * * * *ColE * * * * * * ColF Movie * * * * * *Friday * * * * * * $25 Movie * * * * * Friday * * * * * $26 Movie * * * * * *Friday * * * * * * $26 Movie * * * * * Friday * * * * * $25 Movie * * * * * *Friday * * * * * * $18 Movie * * * * * Friday * * * * * $18 TV * * * * * * * *Tues * * * * * * * $12 Movie * * * * * Tues * * * * * * $21 TV * * * * * * * *Tues * * * * * * * $19 Movie * * * * * Tues * * * * * * $19 TV * * * * * * * *Tues * * * * * * * $21 Movie * * * * * Tues * * * * * * $12 I would like to use a formla that can rank the data when the two criteria in ColD & E are met. *So when Movie and Friday show up in the list of thousands, just return the top 3 values. *Then when Movie and Tues are met, return the top 3 values. I know this can be done, but I am having trouble fixing my formulas when the two criteria change. Thanks in advance for your help.- Hide quoted text - - Show quoted text - I need to allow for changing criteria in the same column. So in two cells in the same two columns, I would have Movie and Wednesday, then in the row beneath this one, I might have TV and Tuesday. The formula above doesn't see that my criteria changed since it is using Absolute references. Let me know if there is a solution. Thanks. Conor |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Top values with changing criteria
So, do you want the top 3 values to be listed side by side, across 3
columns? Enter this formula in F1: =SUMPRODUCT(LARGE(($A$2:$A$13=$D1)*($B$2:$B$13=$E1 )*$C$2:$C$13,COLUMNS($A:A))) Then copy across to H1. Then select F1 to H1. and copy that 3 cell selection down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... On May 15, 5:46 pm, "RagDyer" wrote: Would this work out for you? Duplicates are ranked as individual values: ColA criteria in D1, ColB criteria in E1, Enter formula in F1 and copy down as many rows as the rankings you wish to return. =SUMPRODUCT(LARGE((A$2:A$13=D$1)*(B$2:B$13=E$1)*C$ 2:C$13,ROWS($1:1))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... I am trying to return the top N values from a list with corresponding names. The criteria for ranking changes. ColA:C contain my data (thousands of rows). ColD:E contain my criteria (note below I want to return top 3 values). ColF sorts in Top3 order. ColA ColB ColC ColD ColE ColF Movie Friday $25 Movie Friday $26 Movie Friday $26 Movie Friday $25 Movie Friday $18 Movie Friday $18 TV Tues $12 Movie Tues $21 TV Tues $19 Movie Tues $19 TV Tues $21 Movie Tues $12 I would like to use a formla that can rank the data when the two criteria in ColD & E are met. So when Movie and Friday show up in the list of thousands, just return the top 3 values. Then when Movie and Tues are met, return the top 3 values. I know this can be done, but I am having trouble fixing my formulas when the two criteria change. Thanks in advance for your help.- Hide quoted text - - Show quoted text - I need to allow for changing criteria in the same column. So in two cells in the same two columns, I would have Movie and Wednesday, then in the row beneath this one, I might have TV and Tuesday. The formula above doesn't see that my criteria changed since it is using Absolute references. Let me know if there is a solution. Thanks. Conor |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Top values with changing criteria
On May 18, 7:29*pm, "RagDyer" wrote:
So, do you want the top 3 values to be listed side by side, across 3 columns? Enter this formula in F1: =SUMPRODUCT(LARGE(($A$2:$A$13=$D1)*($B$2:$B$13=$E1 )*$C$2:$C$13,COLUMNS($A:A*))) Then copy across to H1. Then select F1 to H1. and copy that 3 cell selection down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... On May 15, 5:46 pm, "RagDyer" wrote: Would this work out for you? Duplicates are ranked as individual values: ColA criteria in D1, ColB criteria in E1, Enter formula in F1 and copy down as many rows as the rankings you wish to return. =SUMPRODUCT(LARGE((A$2:A$13=D$1)*(B$2:B$13=E$1)*C$ 2:C$13,ROWS($1:1))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message .... I am trying to return the top N values from a list with corresponding names. The criteria for ranking changes. ColA:C contain my data (thousands of rows). ColD:E contain my criteria (note below I want to return top 3 values). ColF sorts in Top3 order. ColA ColB ColC ColD ColE ColF Movie Friday $25 Movie Friday $26 Movie Friday $26 Movie Friday $25 Movie Friday $18 Movie Friday $18 TV Tues $12 Movie Tues $21 TV Tues $19 Movie Tues $19 TV Tues $21 Movie Tues $12 I would like to use a formla that can rank the data when the two criteria in ColD & E are met. So when Movie and Friday show up in the list of thousands, just return the top 3 values. Then when Movie and Tues are met, return the top 3 values. I know this can be done, but I am having trouble fixing my formulas when the two criteria change. Thanks in advance for your help.- Hide quoted text - - Show quoted text - I need to allow for changing criteria in the same column. *So in two cells in the same two columns, I would have Movie and Wednesday, then in the row beneath this one, I might have TV and Tuesday. *The formula above doesn't see that my criteria changed since it is using Absolute references. Let me know if there is a solution. Thanks. Conor- Hide quoted text - - Show quoted text - I would prefer the top 3 to show up as I drag down rows. The criteria to the left of the column will be changed from TV to Movie, and from Tues to Wednesday, etc. So in 3 rows, I would have the same criteria to return the top 3 for that match. Then in the next 3 rows (right below), I would have different criteria (all the same, but different from the first 3) to return the top 3 for that. Does that make sense? If not, I can work to post a sample spreadsheet. Thanks. Conor |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Top values with changing criteria
With this formula you *must* have 3 rows with the *same criteria* for it to
work correctly: =SUMPRODUCT(LARGE((A$2:A$13=D2)*(B$2:B$13=E2)*C$2: C$13,MOD(ROWS($1:1)-1,3)+1)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... On May 18, 7:29 pm, "RagDyer" wrote: So, do you want the top 3 values to be listed side by side, across 3 columns? Enter this formula in F1: =SUMPRODUCT(LARGE(($A$2:$A$13=$D1)*($B$2:$B$13=$E1 )*$C$2:$C$13,COLUMNS($A:A*))) Then copy across to H1. Then select F1 to H1. and copy that 3 cell selection down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... On May 15, 5:46 pm, "RagDyer" wrote: Would this work out for you? Duplicates are ranked as individual values: ColA criteria in D1, ColB criteria in E1, Enter formula in F1 and copy down as many rows as the rankings you wish to return. =SUMPRODUCT(LARGE((A$2:A$13=D$1)*(B$2:B$13=E$1)*C$ 2:C$13,ROWS($1:1))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... I am trying to return the top N values from a list with corresponding names. The criteria for ranking changes. ColA:C contain my data (thousands of rows). ColD:E contain my criteria (note below I want to return top 3 values). ColF sorts in Top3 order. ColA ColB ColC ColD ColE ColF Movie Friday $25 Movie Friday $26 Movie Friday $26 Movie Friday $25 Movie Friday $18 Movie Friday $18 TV Tues $12 Movie Tues $21 TV Tues $19 Movie Tues $19 TV Tues $21 Movie Tues $12 I would like to use a formla that can rank the data when the two criteria in ColD & E are met. So when Movie and Friday show up in the list of thousands, just return the top 3 values. Then when Movie and Tues are met, return the top 3 values. I know this can be done, but I am having trouble fixing my formulas when the two criteria change. Thanks in advance for your help.- Hide quoted text - - Show quoted text - I need to allow for changing criteria in the same column. So in two cells in the same two columns, I would have Movie and Wednesday, then in the row beneath this one, I might have TV and Tuesday. The formula above doesn't see that my criteria changed since it is using Absolute references. Let me know if there is a solution. Thanks. Conor- Hide quoted text - - Show quoted text - I would prefer the top 3 to show up as I drag down rows. The criteria to the left of the column will be changed from TV to Movie, and from Tues to Wednesday, etc. So in 3 rows, I would have the same criteria to return the top 3 for that match. Then in the next 3 rows (right below), I would have different criteria (all the same, but different from the first 3) to return the top 3 for that. Does that make sense? If not, I can work to post a sample spreadsheet. Thanks. Conor |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Top values with changing criteria
Hi,
Try this. Assume your data is in range C3:E14. In cell C18, enter Movie and in cell D18 enter Tuesday. In cell G18, enter =IF(C18="",G17,C18) and copy down a couple of rows. In cell C18, use this array formula (Ctrl+Shift+Enter) =LARGE(($C$3:$C$14=LOOKUP(REPT("z",99),C$18:C18))* ($D$3:$D$14=LOOKUP(REPT("z",99),D$18:D18))*($E$3:$ E$14),COUNTIF(G$18:G18,G18)) Now you can copy this formula down as many rows. Also, keep adding more criteria such as TV and Tuesday -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com wrote in message ... I am trying to return the top N values from a list with corresponding names. The criteria for ranking changes. ColA:C contain my data (thousands of rows). ColD:E contain my criteria (note below I want to return top 3 values). ColF sorts in Top3 order. ColA ColB ColC ColD ColE ColF Movie Friday $25 Movie Friday $26 Movie Friday $26 Movie Friday $25 Movie Friday $18 Movie Friday $18 TV Tues $12 Movie Tues $21 TV Tues $19 Movie Tues $19 TV Tues $21 Movie Tues $12 I would like to use a formla that can rank the data when the two criteria in ColD & E are met. So when Movie and Friday show up in the list of thousands, just return the top 3 values. Then when Movie and Tues are met, return the top 3 values. I know this can be done, but I am having trouble fixing my formulas when the two criteria change. Thanks in advance for your help. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Top values with changing criteria
On May 19, 7:59*pm, "Ashish Mathur" wrote:
Hi, Try this. Assume your data is in range C3:E14. *In cell C18, enter Movie and in cell D18 enter Tuesday. *In cell G18, enter =IF(C18="",G17,C18) and copy down a couple of rows. *In cell C18, use this array formula (Ctrl+Shift+Enter) =LARGE(($C$3:$C$14=LOOKUP(REPT("z",99),C$18:C18))* ($D$3:$D$14=LOOKUP(REPT("*z",99),D$18:D18))*($E$3: $E$14),COUNTIF(G$18:G18,G18)) Now you can copy this formula down as many rows. *Also, keep adding more criteria such as TV and Tuesday -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com wrote in message ... I am trying to return the top N values from a list with corresponding names. *The criteria for ranking changes. ColA:C contain my data (thousands of rows). *ColD:E contain my criteria (note below I want to return top 3 values). *ColF sorts in Top3 order. ColA * * * * * * *ColB * * * * * * *ColC ColD * * * * * *ColE * * * * * * ColF Movie * * * * * *Friday * * * * * * $25 Movie * * * * * Friday * * * * * $26 Movie * * * * * *Friday * * * * * * $26 Movie * * * * * Friday * * * * * $25 Movie * * * * * *Friday * * * * * * $18 Movie * * * * * Friday * * * * * $18 TV * * * * * * * *Tues * * * * * * * $12 Movie * * * * * Tues * * * * * * $21 TV * * * * * * * *Tues * * * * * * * $19 Movie * * * * * Tues * * * * * * $19 TV * * * * * * * *Tues * * * * * * * $21 Movie * * * * * Tues * * * * * * $12 I would like to use a formla that can rank the data when the two criteria in ColD & E are met. *So when Movie and Friday show up in the list of thousands, just return the top 3 values. *Then when Movie and Tues are met, return the top 3 values. I know this can be done, but I am having trouble fixing my formulas when the two criteria change. Thanks in advance for your help.- Hide quoted text - - Show quoted text - Thanks for your help Ashish. That's what I needed. Conor |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Top values with changing criteria
You are welcome.
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com wrote in message ... On May 19, 7:59 pm, "Ashish Mathur" wrote: Hi, Try this. Assume your data is in range C3:E14. In cell C18, enter Movie and in cell D18 enter Tuesday. In cell G18, enter =IF(C18="",G17,C18) and copy down a couple of rows. In cell C18, use this array formula (Ctrl+Shift+Enter) =LARGE(($C$3:$C$14=LOOKUP(REPT("z",99),C$18:C18))* ($D$3:$D$14=LOOKUP(REPT("*z",99),D$18:D18))*($E$3: $E$14),COUNTIF(G$18:G18,G18)) Now you can copy this formula down as many rows. Also, keep adding more criteria such as TV and Tuesday -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com wrote in message ... I am trying to return the top N values from a list with corresponding names. The criteria for ranking changes. ColA:C contain my data (thousands of rows). ColD:E contain my criteria (note below I want to return top 3 values). ColF sorts in Top3 order. ColA ColB ColC ColD ColE ColF Movie Friday $25 Movie Friday $26 Movie Friday $26 Movie Friday $25 Movie Friday $18 Movie Friday $18 TV Tues $12 Movie Tues $21 TV Tues $19 Movie Tues $19 TV Tues $21 Movie Tues $12 I would like to use a formla that can rank the data when the two criteria in ColD & E are met. So when Movie and Friday show up in the list of thousands, just return the top 3 values. Then when Movie and Tues are met, return the top 3 values. I know this can be done, but I am having trouble fixing my formulas when the two criteria change. Thanks in advance for your help.- Hide quoted text - - Show quoted text - Thanks for your help Ashish. That's what I needed. Conor |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
| Return all values found for criteria | Excel Discussion (Misc queries) | |||
| Return across Row Numeric Values Matching EXACT Month & Year for Criteria | Excel Worksheet Functions | |||
| Return Numeric Values Matching EXACT Date for Criteria | Excel Worksheet Functions | |||
| Search multiple values to return single values | Excel Worksheet Functions | |||
Linear Mode
