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
|