[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Find minimum based on grouped criteria

Posted on 2012-08-16
7
Medium Priority
?
382 Views
Last Modified: 2012-08-20
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!
0
Comment
Question by:Shanan212
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 1000 total points
ID: 38301223
The easiest would be to create a pivot table, summarizing your data with the minimum function. See attached file.
pivot-example.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38301314
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
 
LVL 13

Author Comment

by:Shanan212
ID: 38301351
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 39

Expert Comment

by:nutsch
ID: 38301397
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
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 200 total points
ID: 38301641
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
 
LVL 34

Assisted Solution

by:Rob Henson
Rob Henson earned 800 total points
ID: 38311225
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
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 38311398
Thanks all!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question