Maximum value from a pivot table - formula

V

VM

I have a table as shown in the example below. Can you help me with a
formula that pulls out the the project code with maximum hours for an
employee? This
table is from a pivot,

Employee ID Project Number Hours
78904 2057677 140
100 IU 8
530 IU 12
78904 Total 160

87659 2064777 120
2072147 40
87659Total 160

The result should show me like:

Employee ID Project Number Hours
78904 2057677 140
87659 2064777 120

CAn anyone help? Thanks!
 
J

Jacob Skaria

Assuming the table data is in A1:C10 with Row1 as headers please use the
below formula. Col B is project numbers and ColC is hours

=INDEX(B2:B10,MATCH(MAX(C2:C10),C2:C10,0))
 
V

VM

Assuming the table data is in A1:C10 with Row1 as headers please use the
below formula. Col B is project numbers and ColC is hours

=INDEX(B2:B10,MATCH(MAX(C2:C10),C2:C10,0))
--
If this post helps click Yes
---------------
Jacob Skaria










- Show quoted text -

Thanks Jacob. I tried the formula, but it shows me the value of 0..I'm
not sure if I'm doing something wrong?
 
J

Jacob Skaria

If you are having your data in sequence...I mean with no blanks like below
you can use the below formula to get the maximum hours based on employee ID
and project name...

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in the Formula Bar you can notice the curly braces at both ends
"{=<formula>}"

=MAX(IF($A$2:$A$10="EmpID",IF($B$2:$B$10="Project",$C$2:$C$10)))

Suppose you have the data in this format from A2 to C10. Row 1 is having
headers.

Employee ID Project Hours
Emp1 ProA 9
Emp1 ProA 8
Emp1 ProA 7
Emp1 ProB 1
Emp2 ProB 2
Emp1 ProB 3
Emp2 ProC 10
Emp2 ProA 1
Emp2 ProC 30




If this post helps click Yes
 

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