How to Find the Address of the Last Cell in a Column and use this value in another formula

[Emili]

Hello Everyone,

I'm new to excel formulas so I may not be doing this the most efficient way, any advice is much appreciated!

So I have one column filled with numeric data, Lets say its in column A with values filled from A1 to A200. This data will have peaks and troughs, and I can count the number of peaks the data has with the following formula:

=SUMPRODUCT(--(A2:A199>A1:A198),--(A2:A199>A3:A200))

Here this formula requires me to know the 1st, 2nd and 3rd cell address from the top of my column (easy, as it always starts in the same location). It also needs the 1st, 2nd and 3rd cell address from the bottom of my column (HARD! As my data list is often very long, and its time consuming)

I have 2 problems:

1) How to find the cell address of the last filled cell in the column automatically.

I have tried =CELL("ADDRESS",INDEX(A:A,ROWS(A:A),COLUMNS(A:A))), but this gives me the address of the last cell value possible in excel ($A$1048576)

I have also tried to find the last value in a column with =INDEX(A:A,COUNTA(A:A)), and then find the cell address for this value with =CELL("ADDRESS",INDEX((A:A),MATCH(D2,A:A,0))) but this doesn't work because if there are 2 or more values with this number it will retrieve the first cell address which has a match.

2) How can I (if this is possible?) then modify my peak formula to say something like =SUMPRODUCT(--(A2:(coordinate of last value-1)>A1:(coordinate of last value -2),--(A2:(coordinate of last value -1)>A3:A(coordinate of last value)))

Thank you for reading, I've been scratching my head over this for ages!

[thomglea]

Please Login or Register  to view this content.

Change the -1 after (A:A) to go up rows as needed.

[Emili]

Thank you this works perfect for the cell addresses, now how do I put this into my peak formula?

[thomglea]

You will need to use INDIRECT. So, say you wanted to add 20 to the value, it would look like this:

Please Login or Register  to view this content.

[Emili]

I do not need to do anything with the values in the cells of those positions, I just need to insert these cell addresses into my peak formula

I am now stuck once I have the cell addresses of the bottom, second and third from the bottom, how can I modify my peak formula to include these in the range?

[thomglea]

What is your peak formula?

[Emili]

=SUMPRODUCT(--(A2:A199>A1:A198),--(A2:A199>A3:A200))

The cell addresses in bold I have managed to obtain automatically using the formula you provided, which I have put in 3 separate cells.

Now I need to change the range in the formulas, here is an example from the underlined part of my peak formula:

=SUMPRODUCT(--(A2:A199>A1:A198),--(A2:A199>A3:A200))

e.g. from A2:(cell which has the formula to find a cell address 2nd from the bottom of the column --> =CELL("address",OFFSET(A1,COUNTA(A:A)-2,0))

[thomglea]

Please Login or Register  to view this content.

That should do it I think. I do not have your dataset to test it, but the formula at least came back with no syntax error.

The main piece is using this formula over and over to create your cell range reference:

Please Login or Register  to view this content.

[Glenn Kennedy]

I would take an ENTIRELY different approach, avoiding INDIRECT (which - being volatile - can cause performance issues). I'd create 3 Named ranges - called One Two and Three - depending on which row they start in.

Here's ONE:

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(10^10,Sheet1!$A:$A)-2)

It starts at A1 and looks (dynamically) for the last number in the column... and returns the entire range from A1 to the values that is 2 rows (red) UP from the last value. The others are similar (CTRL-F3 to view/edit).

Your formula then becomes:

=SUMPRODUCT(--(Two>One),--(Two>Three))

and will work for any number ov calues in column A.