Using Min & Max to sort log times.

J

J.W. Aldridge

I have a list of data that I am trying to get my peoples actual
production time down.
I have their names in A, Dates in B, Times they moved work in C.
I need to know the time they started working to the last time logged
(min time value vs. the max time value in column C) for each name &
date in A.
D = the min time for Joe, then Amy
E= the max time logged for each.


A B C D E

JOE 2/14 3:01 3:01 3:15
JOE 2/14 3:15
JOE 2/14 3:30
AMY 2/14 3:31 3:31 4:15
AMY 2/14 4:00
AMY 2/14 4:15



Any ideas or suggestions???
 
M

Max

Assume data is in row 2 to 100 in cols A to C as posted

In D2, array-enter ie press CTRL+SHIFT+ENTER:
=IF(COUNTA(A2:B2)<2,"",IF(SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2))>1,"",MIN(IF((A$2:A$100=A2)*(B$2:B$100=B2)*(C$2:C$100>0),C$2:C$100))))

In E2, array-enter ie press CTRL+SHIFT+ENTER:
=IF(COUNTA(A2:B2)<2,"",IF(SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2))>1,"",MAX(IF((A$2:A$100=A2)*(B$2:B$100=B2)*(C$2:C$100>0),C$2:C$100))))

Then select D2:E2, copy down to row100.
Adapt the ranges to suit
 
M

Max

To "array-enter" means to press CTRL+SHIFT+ENTER [CSE] in confirming the
formula (instead of just pressing ENTER). If you did this confirmation
correctly, you should see Excel wrap curly braces: { } around the formula in
the formula bar. If you don't see the curlies, that means it's not correctly
array-entered. Click inside the formula bar and try the CSE again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top