Find Highest Values' Row and Column in Excel

Sanjay Gandhi
Sanjay Gandhi used Ask the Experts™
on
Hi,

I want to find highest values' Row and Column in an array in Excel.
The file is attached for the reference.Get-Highest-Value-in-Array.xlsx

Thanks,

San.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Product
=INDEX(B4:B11,AGGREGATE(15,6,(ROW($C$4:$M$11)-ROW($C$4)+1)/($C$4:$M$11=F15),1))

Open in new window

Month
=INDEX(C3:M3,AGGREGATE(15,6,(COLUMN($C$4:$M$11)-COLUMN($C$4)+1)/(($C$4:$M$11=F15)*(B4:B11=F16)),1))

Open in new window

Get-Highest-Value-in-Array.xlsx
Sanjay GandhiFounder, Kenhal

Author

Commented:
Thanks,

This works great. Can you explain Aggregate and divide and multiply there?

San.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
The AGGREGATE function is similar to SUBTOTAL in that it can perform a number of different tasks: counting , summing, finding minimum or maximum, etc. AGGREGATE(15, ...) finds the nth smallest value in its third parameter. Some of those tasks, such as finding the nth largest or smallest value, may be performed using array expressions without need to array-enter the formula.

Making things even better, AGGREGATE has the ability to ignore both hidden rows and error values when its second parameter is 6. The suggested formulas depend on this latter ability.

The array expression ROW($C$4:$M$11)-ROW($C$4)+1 returns an array of numbers. ROW($C$4:$M$11) returns the row number for each cell in that range. By subtracting the row number of the top left cell and adding 1, you get row index numbers 1 through 8 for each cell in that range.

The denominator in the first formula ($C$4:$M$11=F15) returns an array of TRUE or FALSE values depending on whether the cell value equals the maximum in cell F15. When you choose such an array in an arithmetic expression, TRUE is converted to 1 and FALSE to 0. Because the array expression is in the denominator, (ROW($C$4:$M$11)-ROW($C$4)+1)/($C$4:$M$11=F15) returns an array of row index numbers or DIV/0! error values. AGGREGATE happily ignores the error values and returns the smallest index number for a cell that equals the maximum.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial