| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
last value
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
last value
Hi,
Try this =LOOKUP(2,1/(A1:A52<""),A1:A52) Mike "kevcar40" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
last value
On 4 Sep, 09:22, Mike H wrote:
Hi, Try this =LOOKUP(2,1/(A1:A52<""),A1:A52) Mike "kevcar40" wrote: 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- Hide quoted text - - Show quoted text - thanks mike works a treat |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
last value
On 4 Sep, 10:26, kevcar40 wrote:
On 4 Sep, 09:22, Mike H wrote: Hi, Try this =LOOKUP(2,1/(A1:A52<""),A1:A52) Mike "kevcar40" wrote: 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- Hide quoted text - - Show quoted text - thanks mike works a treat- Hide quoted text - - Show quoted text - is it posible to return the cell address using this formula |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
last value
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" wrote: On 4 Sep, 10:26, kevcar40 wrote: On 4 Sep, 09:22, Mike H wrote: Hi, Try this =LOOKUP(2,1/(A1:A52<""),A1:A52) Mike "kevcar40" wrote: 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- Hide quoted text - - Show quoted text - thanks mike works a treat- Hide quoted text - - Show quoted text - is it posible to return the cell address using this formula |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
last value
On 4 Sep, 11:27, Mike H wrote:
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" wrote: On 4 Sep, 10:26, kevcar40 wrote: On 4 Sep, 09:22, Mike H wrote: Hi, Try this =LOOKUP(2,1/(A1:A52<""),A1:A52) Mike "kevcar40" wrote: 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- Hide quoted text - - Show quoted text - thanks mike works a treat- Hide quoted text - - Show quoted text - is it posible to return the cell address using this formula- Hide quoted text - - 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
last value
Try
=ADDRESS(57,MATCH(6.022*10^23,57:57)) Mike "kevcar40" wrote: On 4 Sep, 11:27, Mike H wrote: 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" wrote: On 4 Sep, 10:26, kevcar40 wrote: On 4 Sep, 09:22, Mike H wrote: Hi, Try this =LOOKUP(2,1/(A1:A52<""),A1:A52) Mike "kevcar40" wrote: 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- Hide quoted text - - Show quoted text - thanks mike works a treat- Hide quoted text - - Show quoted text - is it posible to return the cell address using this formula- Hide quoted text - - 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
