Find minimum based on grouped criteria

Hi,

I am having trouble figuring out how to do this!

I have the following


A       22
A       55
A       23
B       4
B       98
C       0
D       1
D       78
D       2
D       66
D       93
etc

On the column C, I want to populate mimums based on the criteria on column A

eg:

A - the min is 22
B - 4
etc!

Any help is much appreciated!

Thanks!
LVL 13
Shanan212Asked:
Who is Participating?
 
nutschConnect With a Mentor Commented:
The easiest would be to create a pivot table, summarizing your data with the minimum function. See attached file.
pivot-example.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
Try an array formula

=MIN(IF($A$1:$A$11=H22,$B$1:$B$11))

H22 is referring to a cell containing A. You can change it to your own cell
0
 
Shanan212Author Commented:
Ok my mistake, I think I missed a point there in the question. Your answer is helping me but I also want to include this info

ALEX      08/14/12 5:27
ALEX      08/14/12 15:46
ALEX      08/08/12 14:33
ALEX      08/08/12 12:58
ALEX      08/08/12 11:33
CHARA      08/10/12 10:21
CHARA      08/10/12 8:01
CHARA      08/09/12 17:03
CHARA      08/09/12 16:18
DARBA      08/08/12 15:58      
DARBA      08/08/12 14:48      
DARBA      08/08/12 11:39      
DARBA      08/07/12 19:50      
DARBA      08/07/12 19:18      
DARBA      08/07/12 14:13      
DARBA      08/07/12 12:54      

^ I actually have the above info

I want to find out whats Alex's maximum time on each specific date he worked (maximum time = time he exits the workplace)

Eg: Aug 14 -> 15:46
Aug 08 -> 14:33
Chara
Augu 10 -> 10:21, etc

Thanks!
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
nutschCommented:
Then all the more reason to do a pivot, Name and date as row fields, minimum of time as the data field.

Check this question for recommendations on pivot table tutorials, or try some of the following:

http://office.microsoft.com/en-us/excel-help/create-or-delete-a-pivottable-or-pivotchart-report-HP010342375.aspx?CTT=3
http://www.dummies.com/how-to/content/the-essentials-of-excel-2010-pivot-tables-and-pivo.html
http://msdn.microsoft.com/en-us/library/office/aa164458%28v=office.10%29.aspx
                                           

Thomas
0
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
To get the maximum you can also use

=MIN(IF($A$1:$A$11=H22,$B$1:$B$11))


You must press shift-ctrl-enter to enter the formula.
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Shanan212

If you were to split the date and time into separate columns, you would be able to apply a pivot table as suggested by nutsch.

Assuming data in columns A & B, lets set up copy data for the pivot in columns E to G:

Column E  =A1
Column F  =INT(B1)       format as date
Column G =B1-INT(B1)  format as time

Then use columns E to G for the pivot with name and date as row labels and time as data field with minimum set.

Thanks
Rob H
0
 
Shanan212Author Commented:
Thanks all!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.