last value

[kevcar40]

hi
i ahave a worksheet that the user enters a score for each week
wk 1 =a5.............wk 52 = a57
what i want to do is chack the range (a5:a57) and find the last entry
so if wk33 has a value of 1500 entered and wk34 has a value of 2000
entered
i want to return 2000

how do i go about finding last value

thanks

kevin

 

[Mike H]

Hi,

Try this

=LOOKUP(2,1/(A1:A52<>""),A1:A52)

Mike

 

[kevcar40]

Hi,

Try this

=LOOKUP(2,1/(A1:A52<>""),A1:A52)

Mike






- Show quoted text -

thanks mike works a treat

 

[kevcar40]

thanks mike works a treat- Hide quoted text -

- Show quoted text -

is it posible to return the cell address using this formula

 

[Mike H]

Hi,

You can use another mthod for that which is an array formula

=ADDRESS(MAX(IF(LEN(A1:A1000)>0,ROW(A1:A1000),0)),2)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[kevcar40]

Hi,

You can use another mthod for that which is an array formula

=ADDRESS(MAX(IF(LEN(A1:A1000)>0,ROW(A1:A1000),0)),2)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike






- Show quoted text -

thanks again works great
i have tried modifying it to test the following range E57:bd57 but it
returns $b$57 for some reason
i changed A1:a1000 to e57:bd57
the answer i was expecting was Am57

 

[Mike H]

Try

=ADDRESS(57,MATCH(6.022*10^23,57:57))

Mike

thanks again works great
i have tried modifying it to test the following range E57:bd57 but it
returns $b$57 for some reason
i changed A1:a1000 to e57:bd57
the answer i was expecting was Am57