| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with formula
I am currently using the following formulas to pull the #1 - #5 most common
occurances from a range. Now I need to do the same thing, but I need it to compare the results to another column before returning the results. {=INDEX(Terms!$O$2:$O$65500,MODE(IF(Terms!$O$2:$O$ 65500<"",MATCH(Terms!$O$2:$O$65500,Terms!$O$2:$O$ 65500,0))))} {=INDEX(Terms!$O$2:$O$20000,MODE(IF(Terms!$O$2:$O$ 20000<"",IF(COUNTIF(H$177:H177,Terms!$O$2:$O$2000 0)=0,MATCH(Terms!$O$2:$O$20000,Terms!$O$2:$O$20000 ,0)+{0,0}))))} Right now these formulas return the result off one range, but I need it to return the same result but after comparing it too a second range (i.e. Terms!$N2:$N65500<90). I just can't figure out how to add it to this equation to make it work. Using Excel 2003 Thanks in advance! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula
Just add another IF:
....MODE(IF(Terms!$O$2:$O$65500<"",IF(Terms!$N2:$ N65500<90,MATCH... Or, you could multiply the 2 arrays together like this: ....MODE(IF((Terms!$O$2:$O$65500<"")*(Terms!$N2:$ N65500<90),MATCH... Note that if any cells in Terms!$N2:$N65500 are empty they will evaluate to be <90. Which means you might have to add another array: ....MODE(IF((Terms!$O$2:$O$65500<"")*(Terms!$N2:$ N65500<90)*(Terms!$N2:$N65500<""),MATCH... Follow the same syntax for the second formula but *make sure* the range refs are all the same size. Since you're referencing such a large range *expect* this to be slow! -- Biff Microsoft Excel MVP "John" wrote in message ... I am currently using the following formulas to pull the #1 - #5 most common occurances from a range. Now I need to do the same thing, but I need it to compare the results to another column before returning the results. {=INDEX(Terms!$O$2:$O$65500,MODE(IF(Terms!$O$2:$O$ 65500<"",MATCH(Terms!$O$2:$O$65500,Terms!$O$2:$O$ 65500,0))))} {=INDEX(Terms!$O$2:$O$20000,MODE(IF(Terms!$O$2:$O$ 20000<"",IF(COUNTIF(H$177:H177,Terms!$O$2:$O$2000 0)=0,MATCH(Terms!$O$2:$O$20000,Terms!$O$2:$O$20000 ,0)+{0,0}))))} Right now these formulas return the result off one range, but I need it to return the same result but after comparing it too a second range (i.e. Terms!$N2:$N65500<90). I just can't figure out how to add it to this equation to make it work. Using Excel 2003 Thanks in advance! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula
Thanks...works well, but I'm having one issue. It seems to skip over results
in the list that have the same number of occurances. Example: Violations - 20** Failed Training - 15 Job Abandonment - 15 Another Job - 11 Personal Reasons - 8 Dissatisifed - 6 ** Represents the # of times this occurs in the list. When I use this equation... {=INDEX(Terms!$O$2:$O$10000,MODE(IF((Terms!$O$2:$O $10000<"")*(Terms!$N$2:$N$10000<90)*(Terms!$N$2:$ N$10000<""),MATCH(Terms!$O$2:$O$10000,Terms!$O$2: $O$10000,0))))} It reports: Violations Failed Training Another Job Personal Reasons Dissatisfied It skips over "Job Abandonment" because the count is the same. Any ideas? Thanks Again! "T. Valko" wrote: Just add another IF: ....MODE(IF(Terms!$O$2:$O$65500<"",IF(Terms!$N2:$ N65500<90,MATCH... Or, you could multiply the 2 arrays together like this: ....MODE(IF((Terms!$O$2:$O$65500<"")*(Terms!$N2:$ N65500<90),MATCH... Note that if any cells in Terms!$N2:$N65500 are empty they will evaluate to be <90. Which means you might have to add another array: ....MODE(IF((Terms!$O$2:$O$65500<"")*(Terms!$N2:$ N65500<90)*(Terms!$N2:$N65500<""),MATCH... Follow the same syntax for the second formula but *make sure* the range refs are all the same size. Since you're referencing such a large range *expect* this to be slow! -- Biff Microsoft Excel MVP "John" wrote in message ... I am currently using the following formulas to pull the #1 - #5 most common occurances from a range. Now I need to do the same thing, but I need it to compare the results to another column before returning the results. {=INDEX(Terms!$O$2:$O$65500,MODE(IF(Terms!$O$2:$O$ 65500<"",MATCH(Terms!$O$2:$O$65500,Terms!$O$2:$O$ 65500,0))))} {=INDEX(Terms!$O$2:$O$20000,MODE(IF(Terms!$O$2:$O$ 20000<"",IF(COUNTIF(H$177:H177,Terms!$O$2:$O$2000 0)=0,MATCH(Terms!$O$2:$O$20000,Terms!$O$2:$O$20000 ,0)+{0,0}))))} Right now these formulas return the result off one range, but I need it to return the same result but after comparing it too a second range (i.e. Terms!$N2:$N65500<90). I just can't figure out how to add it to this equation to make it work. Using Excel 2003 Thanks in advance! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula
Disregaurd last post...I found the problem. Thanks again for the help!
"John" wrote: Thanks...works well, but I'm having one issue. It seems to skip over results in the list that have the same number of occurances. Example: Violations - 20** Failed Training - 15 Job Abandonment - 15 Another Job - 11 Personal Reasons - 8 Dissatisifed - 6 ** Represents the # of times this occurs in the list. When I use this equation... {=INDEX(Terms!$O$2:$O$10000,MODE(IF((Terms!$O$2:$O $10000<"")*(Terms!$N$2:$N$10000<90)*(Terms!$N$2:$ N$10000<""),MATCH(Terms!$O$2:$O$10000,Terms!$O$2: $O$10000,0))))} It reports: Violations Failed Training Another Job Personal Reasons Dissatisfied It skips over "Job Abandonment" because the count is the same. Any ideas? Thanks Again! "T. Valko" wrote: Just add another IF: ....MODE(IF(Terms!$O$2:$O$65500<"",IF(Terms!$N2:$ N65500<90,MATCH... Or, you could multiply the 2 arrays together like this: ....MODE(IF((Terms!$O$2:$O$65500<"")*(Terms!$N2:$ N65500<90),MATCH... Note that if any cells in Terms!$N2:$N65500 are empty they will evaluate to be <90. Which means you might have to add another array: ....MODE(IF((Terms!$O$2:$O$65500<"")*(Terms!$N2:$ N65500<90)*(Terms!$N2:$N65500<""),MATCH... Follow the same syntax for the second formula but *make sure* the range refs are all the same size. Since you're referencing such a large range *expect* this to be slow! -- Biff Microsoft Excel MVP "John" wrote in message ... I am currently using the following formulas to pull the #1 - #5 most common occurances from a range. Now I need to do the same thing, but I need it to compare the results to another column before returning the results. {=INDEX(Terms!$O$2:$O$65500,MODE(IF(Terms!$O$2:$O$ 65500<"",MATCH(Terms!$O$2:$O$65500,Terms!$O$2:$O$ 65500,0))))} {=INDEX(Terms!$O$2:$O$20000,MODE(IF(Terms!$O$2:$O$ 20000<"",IF(COUNTIF(H$177:H177,Terms!$O$2:$O$2000 0)=0,MATCH(Terms!$O$2:$O$20000,Terms!$O$2:$O$20000 ,0)+{0,0}))))} Right now these formulas return the result off one range, but I need it to return the same result but after comparing it too a second range (i.e. Terms!$N2:$N65500<90). I just can't figure out how to add it to this equation to make it work. Using Excel 2003 Thanks in advance! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula
You're welcome!
-- Biff Microsoft Excel MVP "John" wrote in message ... Disregaurd last post...I found the problem. Thanks again for the help! "John" wrote: Thanks...works well, but I'm having one issue. It seems to skip over results in the list that have the same number of occurances. Example: Violations - 20** Failed Training - 15 Job Abandonment - 15 Another Job - 11 Personal Reasons - 8 Dissatisifed - 6 ** Represents the # of times this occurs in the list. When I use this equation... {=INDEX(Terms!$O$2:$O$10000,MODE(IF((Terms!$O$2:$O $10000<"")*(Terms!$N$2:$N$10000<90)*(Terms!$N$2:$ N$10000<""),MATCH(Terms!$O$2:$O$10000,Terms!$O$2: $O$10000,0))))} It reports: Violations Failed Training Another Job Personal Reasons Dissatisfied It skips over "Job Abandonment" because the count is the same. Any ideas? Thanks Again! "T. Valko" wrote: Just add another IF: ....MODE(IF(Terms!$O$2:$O$65500<"",IF(Terms!$N2:$ N65500<90,MATCH... Or, you could multiply the 2 arrays together like this: ....MODE(IF((Terms!$O$2:$O$65500<"")*(Terms!$N2:$ N65500<90),MATCH... Note that if any cells in Terms!$N2:$N65500 are empty they will evaluate to be <90. Which means you might have to add another array: ....MODE(IF((Terms!$O$2:$O$65500<"")*(Terms!$N2:$ N65500<90)*(Terms!$N2:$N65500<""),MATCH... Follow the same syntax for the second formula but *make sure* the range refs are all the same size. Since you're referencing such a large range *expect* this to be slow! -- Biff Microsoft Excel MVP "John" wrote in message ... I am currently using the following formulas to pull the #1 - #5 most common occurances from a range. Now I need to do the same thing, but I need it to compare the results to another column before returning the results. {=INDEX(Terms!$O$2:$O$65500,MODE(IF(Terms!$O$2:$O$ 65500<"",MATCH(Terms!$O$2:$O$65500,Terms!$O$2:$O$ 65500,0))))} {=INDEX(Terms!$O$2:$O$20000,MODE(IF(Terms!$O$2:$O$ 20000<"",IF(COUNTIF(H$177:H177,Terms!$O$2:$O$2000 0)=0,MATCH(Terms!$O$2:$O$20000,Terms!$O$2:$O$20000 ,0)+{0,0}))))} Right now these formulas return the result off one range, but I need it to return the same result but after comparing it too a second range (i.e. Terms!$N2:$N65500<90). I just can't figure out how to add it to this equation to make it work. Using Excel 2003 Thanks in advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
