I want to know which number appears most frequently within a table by instructor code. For example, I want to produce a report that searches column A for an instructor code (for example, any instructor code "ba*") and then finds the mode of the corresponding cell in column B. I know I could manually set up Excel to figure it, but I am just positive there is an automatic way to do it.

Let me try to rephrase it one more time for clarity's sake: Display the mode of each class by instructor.

NOTES ABOUT THE FILE I HAVE UPLOADED:

• I'm using Excel 2007.
• If you look at the file I have attached, I attempted to do this in by taking the data from worksheet "data" and putting it into a table in worksheet "overview." When I couldn't get that to work, I started using Pivot Tables in worksheet "data" and managed to get all the functions I wanted (average, max, and min) except for mode.
• There are still many exams to be graded, so at the current time, not every class has data.

The mode is a straight function against the data:

=MODE(V1:V20)
(just adjust the range properly, Excel 2003 doesn't automatically account for blank cells. I think the advanced function xlpMODE on 2007 does.)

If you want to use more advanced Excel 2007 versions, have a read through this page see if it gives you what you're looking for:

xlpMODE

Quote:

---

Originally Posted by JBeaucaire

(just adjust the range properly)

---

I'll try the advanced version you provided the link for, but I know how to use Mode normally. I was asking for help with calculating the mode if a corresponding cell meets a specified criteria. I am actually trying to avoid having to manually adjust the range.

Yeah, avoiding the range adjustments would be the ticket, huh? I see what you mean.

This isn't something that would/could be designed to reference static ranges? It's varying too much to be simple to formulize?

You know, once that all the test scores have been entered, it shouldn't be a problem to sort by instructor name, name those ranges, and just have it calculate Average, Min, Max, Mode, and Count by those named ranges. I was trying to make it too complicated. :P

Thanks for the help!