Using Min & Max to sort log times.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J.W. Aldridge - 14 Feb 2008 18:58 GMT

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???

Reply to this Message

Max - 15 Feb 2008 03:37 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>
> I have a list of data that I am trying to get my peoples actual
[quoted text clipped - 16 lines]
>
> Any ideas or suggestions???

Reply to this Message

Max - 15 Feb 2008 19:42 GMT

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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

J.W. Aldridge - 22 Feb 2008 22:48 GMT

Thanx!!!!

Reply to this Message

Max - 23 Feb 2008 02:22 GMT

welcome, it was good of you to feedback here.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thanx!!!!

Reply to this Message