Mode function with IF statements

Question

I want to use the MODE function to find the most common number given some other conditions.

I want to use the formulas below to find the most common and the second most common result in my set but I need to add some conditions first:

Cell C1

=MODE(D5:D5002)

&

Cell C2

{=MODE(IF(COUNTIF(C1,D5:D5002)=0,D5:D5002))}

What I want to do is also reference Column J to determine if the results from MODE, above, fall within a certain date range.

Cell A1 contains the starting date and Cell A2 contains the ending date.

Column D is all ID Numbers and Column J is the date they accessed the system.

I want to determine the employees that access the system the most over a date range.

Any help on how to do this would be great. Thanks

Answer 1

Try this,

C1 enter 'Common Numbers'

in C2, with CTRL+SHIFT+ENTER,

=IFERROR(MODE(IF(J$5:J$5002>=A$1,IF(J$5:J$5002<=A$2,IF(ISNA(MATCH(D$5:D$5002,C$1:C1,0)),D$5:D$5002)))),"")

then copy down.

Answer 2

Upload the sample data on SkyDrive to help us understand the issue better. Refer the link given below to upload the data on SkyDrive:

http://answers.microsoft.com/en-us/windows/forum/windows_7-security/how-do-i-post-a-screen-shot-on-this-forum/c86de820-c620-401c-a804-9f6337cd3053