CODEWITHSUNDEEP
excelworksheet-function
user2957394
user2957394

Reputation: 233

How to find the first and second maximum number?

I am trying to find first highest number and second highest number in excel. What shall i do for that. I did not find the right formula.

Note: I have already used the large and max formula.

=LARGE(E4:E9;1)

edit: guys I know if i write 2 instead of 1 i will get the result but i have to click the mouse to see all result.

Upvotes: 19

Views: 142504

Answers (4)

virolino
virolino

Reputation: 2201

If the max value appears several times, then the MAX and the second MAX will be the same, if you apply the LARGE formula directly.

If you are interested in filtering out duplicates, try:

=LARGE(UNIQUE($E$4:$E$9),1)

for the MAX value and

=LARGE(UNIQUE($E$4:$E$9),2)

for the second MAX value.

You can join these values to be displayed in one cell at the same time. Example:

=LARGE(UNIQUE($E$4:$E$9),1)&" > "&LARGE(UNIQUE($E$4:$E$9),2)

(in the sense that the MAX is bigger than the second MAX).

NOTE: In the formulas, use comma (,) or semi-colon (;), depending on your system configuration - Excel expects one OR the other. I used comma because that is what my Excel wants.

Upvotes: 0

MMALI3287
MMALI3287

Reputation: 19

Try this,

=MAX(E4:E9) & ", " & LARGE(E4:E9, 2)

Upvotes: 1

user2957394
user2957394

Reputation: 233

OK I found it.

=LARGE($E$4:$E$9;A12)

=large(array, k)

Array Required. The array or range of data for which you want to determine the k-th largest value.

K Required. The position (from the largest) in the array or cell range of data to return.

Upvotes: 2

barry houdini
barry houdini

Reputation: 46331

If you want the second highest number you can use

=LARGE(E4:E9;2)

although that doesn't account for duplicates so you could get the same result as the Max

If you want the largest number that is smaller than the maximum number you can use this version

=LARGE(E4:E9;COUNTIF(E4:E9;MAX(E4:E9))+1)

Upvotes: 30

Related Questions