Formula to show the large/biggest of the last 50 values of a column

**dualaudio454252** · 06-26-2013, 09:45 PM

Please, I need a formula, preferably not a array one, to show the large/biggest of the last 50 values (non blank cells) of a column.
I know, could delete blank cells, but I don't want to rearrange each column, they must keep the way they are.
And I have to do this to various rows.
See attached file.
The formula should mention only the last line to be searched, so I would paste it bellow this line, and could drag when the column becomes longer.

**jeffreybrown** · 06-26-2013, 10:13 PM

I don't know of a non-array formula that can do this, but for an array...

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**dualaudio454252** · 06-26-2013, 10:28 PM

I did CTRL+SHIFT+ENTER, but it shows a error message in:
9.9E

**dualaudio454252** · 06-26-2013, 10:31 PM

Oh, I got it:
http://www.excelforum.com/excel-form...o-columns.html
Can it be another expression or value?
The largest number is under 1000.

**dualaudio454252** · 06-26-2013, 11:16 PM

It works for me this way:
=LARGE(INDEX(G$1:G221,LARGE(IF(ISNUMBER(G$1:G221),ROW(G$1:G221)-ROW(G$1)+1),50)):INDEX(G$1:G221,MATCH(1E+99,G$1:G221)),1)
+
CTRL+SHIFT+ENTER
Thank you so much, Jeffreybrown!!!!!