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

My Account

Check out

Shopping Cart

JOIN OUR NEWSLETTER
and receive for joining:
	Free eBook Learn More!
	Free Excel ADD-IN
	Free Weekly Excel Tip








F1 eBook (Spanish)

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

CATEGORY: Excel Date & Time Formulas

VERSIONS: All Microsoft Excel Versions

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))}

ID______Login Time______Logout Time
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.80
VIEWS: 15997

Email Tip

Print Tip

Add to My Tips

READER COMMENTS (view all comments)

PerNielsen wrote on May 16, 2005 11:43 EST

I like the idea, but there seems to be a problem with the 24-hour clock. Since this clock starts at 00:00 and there is no 24:XX values, the 00:00 condition must be handled properly.

The suggested formula:

=1/MAX((A14=$A$2:$A$10)*($B$2:$B$10<>0)*(1/$B$2:$B$10))

will always lead to a divide-by-zero error if the time 00:00 is in the table. This in turn causes the MAX() function to produce a divide-by-zero error.

The formula below solves that problem by including a very large value for the MAX() function to choose in case a 00:00 time is in the table. The reciprocal will then be small enough for the formula to return 0:00, as expected.

=1/MAX((A14=$A$2:$A$10)*IF(ISERROR(1/$B$2:$B$10),10^6,1/$B$2:$B$10))

Returns #value!
samad wrote on May 27, 2005 01:21 EST

This formula shows results #value! when I same copied to excel sheet. I checked format of login time & logout time but it could not solve the problem Please guide me .
Thanks
[email]xxsamad.gadit@gmail.com[/email]
(remove xx)

Reply: samad
Alan wrote on May 30, 2005 00:01 EST

Hi samad,

[QUOTE=samad]This formula shows results #value! when I same copied to excel sheet. I checked format of login time & logout time but it could not solve the problem Please guide me .
Thanks
[email]xxsamad.gadit@gmail.com[/email]
(remove xx)[/QUOTE]Which formula? Also, what are the values in the source data cells?

Thanks,

Alan.

samad wrote on May 30, 2005 00:46 EST

Hi Alan

[=1/MAX((A14=$A$2:$A$10)*($B$2:$B$10<>0)*(1/$B$2:$B$10))]

The formula describe in this tip. I hope now it clerify.
Thanks for taking interest & hope you'll reply soon.

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))}

ID______Login Time______Logout Time
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

mangesh_yadav wrote on May 30, 2005 01:05 EST

Hi Samad,

The formula works perfectly fine. But you are not using it as an array formula. After entering the formula, you need to press control - shift - enter.

Mangesh

samad wrote on May 30, 2005 01:16 EST

Thank you Mangesh this is new thing to learn for me

[email]xxsamad.gadit@gmail.com[/email]
(remove xx)

mangesh_yadav wrote on May 30, 2005 01:33 EST

Thanks for the feedback.

Mangesh

Subtotals
Anis Shekha wrote on June 14, 2005 06:54 EST

Can any one please help me find the tip "Deleting the Word "Total" from the subtotal List"

Reply: Anis Shekha
Alan wrote on June 15, 2005 01:44 EST

Hi Anis,

[QUOTE=Anis Shekha]Can any one please help me find the tip "Deleting the Word "Total" from the subtotal List"[/QUOTE]Just google it:

[url]http://www.google.co.nz/search?hl=en&q=%22Deleting+the+Word+%22%22Total%22%22+from+the+subtotal+List%22&meta=[/url]

OR

[url]http://tinyurl.com/bq4wr[/url]

Alan.

Special Edition Using Microsoft Word 2002

Your First Business Plan: A Simple Question and Answer Format Designed to Help You Write Your Own Plan (3rd Ed)

Real Estate Finance and Investments (Real Estate Finance and Investments, 11th Ed)

422 Tax Deductions for Businesses and Self-Employed Individuals : You Get a Raise Every Time You Find a Legitimate Tax Deduction

The Basics of Finance: Financial Tools for Non Financial Managers

Microsoft PowerPoint Version 2002 Step by Step

RELATED MICROSOFT EXCEL TIPS

• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books

VIEW ALL BOOKS

	Advertise With Us
Tips	Add-In in VBA \| Applications - Word, Outlook in VBA \| Array Formulas \| Cells, Ranges, Rows, and Columns in VBA \| Counting \| Custom Functions \| Custom Functions in VBA \| Database Formulas \| Database in VBA \| Date & Time Formulas \| Date & Time in VBA \| Events in VBA \| Excel 2003 \| Excel Chart \| Excel Consolidating \| Excel Counting \| Excel Custom Functions using VBA \| Excel Customizing \| Excel Data \| Excel Dates \| Excel Editing \| Excel Files \| Excel Filter \| Excel Format \| Excel Formula \| Excel General \| Excel Grouping and Outlining \| Excel Importing Text Files \| Excel Information \| Excel Keyboard Shortcuts \| Excel Loan Formulas \| Excel Macros - VBA \| Excel Pivot Tables \| Excel Printing \| Excel Range Name \| Excel Security - Protection \| Excel Sorting \| Excel Style \| Excel Subtotals \| Excel Summing \| Excel Text \| Excel Time \| Excel Tools \| Excel Worksheet, Workbook \| Files, Workbook, and Worksheets in VBA \| Financial Formulas \| Formating in VBA \| General Topics in VBA \| Import and Export in VBA \| Information Formulas \| Keyboard & Other Shortcuts in VBA \| Keyboard Formula Shortcuts \| Links between Worksheet and Workbooks \| Links in VBA \| Logical Formulas \| Lookup Formulas \| Mail - Send and Receive in VBA \| Menus, Toolbars, Status bar in VBA \| Modules, Class Modules in VBA \| Other Q&A Formulas \| Printing in VBA \| Protecting in VBA \| Summing \| Text Formulas \| User Forms, Input boxes in VBA \| Using Loops \| Working with Formulas \|
Tips by Version	Microsoft Excel 97 \| Microsoft Excel 2000 \| Microsoft Excel 2002 \| All Microsoft Excel Versions \| New in Excel 2002 \| New in Excel 2003 - Office 11
Website	Home \| Tip of Hour \| Recommended Tips \| Most Viewed Tips \| Tips by Version \| Submit a Tip \| My Tips Microsoft Excel Tutorials \| Excel Links \| Write for Us \| About Us \| Search Results \| Tip Archives \| Excel Forum \| Excel Forum Archives
Excel Book	Excel 97 Book \| Excel 2000 Book \| Excel 2002 Book \| Excel XP Book \| Book Store

Terms and Conditions of use The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Copyright © 2003 ExcelTip.com Microsoft, Microsoft Excel is a U.S. registered trademark of Microsoft Corporation Site Developed By: Varien