#1   Report Post  
Old July 14th 12, 10:29 AM
Junior Member
 
First recorded activity by ExcelBanter: Jul 2012
Posts: 1
Default Min, Max

Good day...
Im just new in using ms excel 2007.
Please help me this scenario.

How to get the min and the max in this table base on the names on column A and time on column B.

Column A Column B

James 7:00 AM
James 8:55 AM
James 5:30 PM
Cameron 7:00 AM
Cameron 5:15 PM
George 8:56 AM
George 9:30 AM
George 11:00AM
George 2:59 PM

The resulting table will look like this

Column A Column B

Cameron 7:00 AM
Cameron 5:15 PM
George 8:56 AM
George 2:59 PM
James 7:00 AM
James 5:30 PM

I know it will be easy to you Excel Experts as for me.... it gives me hell of head ache.

  #2   Report Post  
Old July 14th 12, 02:54 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,729
Default Min, Max

Hi Jonz,

Am Sat, 14 Jul 2012 09:29:30 +0000 schrieb jonz erazol:

How to get the min and the max in this table base on the names on column
A and time on column B.

Column A Column B

James 7:00 AM
James 8:55 AM
James 5:30 PM
Cameron 7:00 AM
Cameron 5:15 PM
George 8:56 AM
George 9:30 AM
George 11:00AM
George 2:59 PM


try:
=MIN(WENN(A1:A100="Cameron";B1:B100))
and
=MAX(WENN(A1:A100="Cameron";B1:B100))
enter both formulas with CTRL+Shift+Enter

Or try it with Pivot-Table - drag names into rows and time two times to
values and choose for time max and min


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #3   Report Post  
Old July 14th 12, 02:58 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,729
Default Min, Max

Hi Jonz,

Am Sat, 14 Jul 2012 15:54:54 +0200 schrieb Claus Busch:

try:
=MIN(WENN(A1:A100="Cameron";B1:B100))
and
=MAX(WENN(A1:A100="Cameron";B1:B100))
enter both formulas with CTRL+Shift+Enter


sorry that I posted the formulas in German. Here now in English:
=MIN(IF(A1:A100="James",B1:B100))
and
=MAX(IF(A1:A100="James",B1:B100))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #4   Report Post  
Old July 14th 12, 03:41 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default Min, Max

On Sat, 14 Jul 2012 09:29:30 +0000, jonz erazol wrote:


Good day...
Im just new in using ms excel 2007.
Please help me this scenario.

How to get the min and the max in this table base on the names on column
A and time on column B.

Column A Column B

James 7:00 AM
James 8:55 AM
James 5:30 PM
Cameron 7:00 AM
Cameron 5:15 PM
George 8:56 AM
George 9:30 AM
George 11:00AM
George 2:59 PM

The resulting table will look like this

Column A Column B

Cameron 7:00 AM
Cameron 5:15 PM
George 8:56 AM
George 2:59 PM
James 7:00 AM
James 5:30 PM

I know it will be easy to you Excel Experts as for me.... it gives me
hell of head ache.


If a different layout would be OK, you could use a Pivot Table.
Label each column: for Example Names Times

Insert / Pivot Table

Drag Names to Row Labels
Drag Times to Values
Change Value Field Settings to Min
Adjust format to Time
Drag Times to Values
Change Value Field Settings to Max
Adjust format to Time

Format and relabel areas to Taste

Results could be:

Times
Name Min Max
Cameron 5:15 AM 7:00 AM
George 2:59 AM 11:00 AM
James 5:30 AM 8:55 AM


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 12:49 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017