How to find the first and second maximum number?

excel worksheet-function
user2957394 picture user2957394 · Nov 15, 2013 · Viewed 140.1k times · Source

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.

Answer

barry houdini picture barry houdini · Nov 15, 2013

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)

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Get values from other sheet using VBA

I want to get values from other sheets. I have some values in Excel (sheet2) for example: A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6 I sum each column in row 4. I'm working with these values in sheet2 but I …

Read More
Excel formula to get cell color

I would like to know if we can find out the Color of the CELL with the help of any inline formula (without using any macros) I'm using Home User Office package 2010.

Read More