|

# » Finding the maximum value of all those meeting a certain criteria.

Problem:

Range A2:C10 contains the login and logout times of various ID's.
Each ID could log in and out a number of times a day.
How could we find the first time a specific ID logged in and the last time that same ID logged out?

Solution:

For each of the ID's in range A2:A10, enter two Array Formulas.
To find the first login time (Column B) enter the following formula:
{=1/MAX((A14=\$A\$2:\$A\$10)*(\$B\$2:\$B\$10<>0)*(1/\$B\$2:\$B\$10))}
To find the last logout time (Column C) enter the following formula:
{=MAX((\$A\$2:\$A\$10=A14)*(\$C\$2:\$C\$10))}

1 ______02:40___________03:10
2 ______00:15___________03:20
1 ______06:20___________09:30
3 ______09:14___________11:05
4 ______11:00___________19:30
2 ______04:05___________06:55
3 ______12:08___________17:17
1 ______10:00___________16:20
2 ______08:12___________12:33

ID______First Login Time______Last Logout Time
1 ______2:40__________________16:20
2 ______0:15__________________12:33
3 ______9:14__________________17:17
4 ______11:00_________________19:30

Screenshot // Finding the maximum value of all those meeting a certain criteria.

Rate This Tip
 1 2 3 4 5
Rating: 3.14     Views: 34453
No comments have been submitted.
Name
Comment Title