Clever Geek Handbook

How to get max and min value with other column values ​​in excel?

I want to get max and min values ​​with different column values ​​in ms excel 2007. I have two columns

| Column A | Column B |
-----------------------
|A         |   18     |
-----------------------
|B         |   78     |
-----------------------
|c         |   9      |

I want to print the maximum values ​​and minimum values ​​"Column B" with values ​​"Column A". Please suggest a formula for me.

+3


source to share


2 answers


The following formula will find the maximum value in column B and return the value from column A that is on one row:

=INDEX(A:A,(MATCH(MAX(B:B),B:B,0)))

In your example, the result will be "B".



Note that if the maximum value occurs in column B more than once, only the first will be processed with this formula.

For minimum, just replace MIN instead of MAX.

+3


source


One way: select a cell to output data, for example. D2. Then select

Data> Consolidation ... Function: Min., Ref: $ A: $ B, Use Labels In: Left Column



and repeat using the function: Max.

Another way is to use a pivot table.

0


source






More articles:


All Articles