Last cell entry in column - No VBA please

PeterBrazel

New Member
Joined
May 9, 2002
Messages
35
Can somebody help me by indicating how I can locate the last cell in a column that contains a numerical value. Alternatively the last row of a database.

Thanks in advance
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Hi,

=MATCH(9.99999999999999E+307,A:A)

will tell you what cell the last value is in.

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A),1)

will return its value.

Paddy
 

PeterBrazel

New Member
Joined
May 9, 2002
Messages
35
Not quite there. That formula seems to return the row number I guess I need the cell address.

I am trying to find the min and the max of a column in a database.

Thanks
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
=ADDRESS(MATCH(9.99999999999999E+307,A:A),MATCH(9.99999999999999E+307,A:A),4,1)


Paddy
 

PeterBrazel

New Member
Joined
May 9, 2002
Messages
35

ADVERTISEMENT

Paddy,

This is what I tried doing

=MIN(Nikkei!$M$4,M&MATCH(9.99999999999999E+307,Nikkei!M:M))

It failed. Without the concatination it returned 23 which is the row without the column address.
 

PeterBrazel

New Member
Joined
May 9, 2002
Messages
35

ADVERTISEMENT

Paddy,

Sorry about this but something is wrong. To test it I placed various numbers in a new sheet in column A then in a cell in column B I placed your formula it returned A1 which was not the minimum number.

Also it is the value of the number I want.

Thanks again in advance.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
sorry - vital bit missing:

=ADDRESS(MATCH(MIN(A:A),A:A,0),1,4,1)

for the address.

If you just want the value, isn't:

=min(a:a)

all you need?

paddy
 

PeterBrazel

New Member
Joined
May 9, 2002
Messages
35
Paddy,

Yes I thin MIN(A:A) will do but it must be the sheet reference that is causing the problem. I have multiple sheets and the the formula needs to go to a sheet called Nikkei and obtain this value from column M.

=MIN(NIKKEI!M:NIKKEI!M) does not work but the MIN function works OK within the current sheet.

Perhaps I need a break.

Thanks again.
 

Forum statistics

Threads
1,147,674
Messages
5,742,541
Members
423,736
Latest member
dracula cyrus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top