Help with formula

[John]

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$20000)=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!

 

[T. Valko]

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!

 

[John]

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!

 

[John]

Disregaurd last post...I found the problem. Thanks again for the help!

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Disregaurd last post...I found the problem. Thanks again for the help!