for example :
cells
a1
a2
a3 20
a4 30
a5 40
formula to give value from the first cell with data
a separate formula to give value from the last cell with data
cells
a1 11
a2 10
a3 20
a4 30
a5
Please help.
Thank you.
for example :
cells
a1
a2
a3 20
a4 30
a5 40
formula to give value from the first cell with data
a separate formula to give value from the last cell with data
cells
a1 11
a2 10
a3 20
a4 30
a5
Please help.
Thank you.
Hi Try this
=INDEX(A1:A5,MIN(IF(A1:A5<>"",ROW(A1:A5),"")))
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
=LOOKUP(2,1/--(A1:A5<>""),A1:A5)
Cheers!
Deep Dave
Thanks.
Is it possible to have a non-array formula?
Or try:
=INDEX(A1:A5,MATCH(TRUE,INDEX(A1:A5<>"",0),0))
Try this for 1st Non-Blank value.
=INDEX(A1:A5,MATCH(TRUE,INDEX((A1:A5<>0),0),0))
For Last Non Blank Value
=LOOKUP(2,1/--(A1:A5<>""),A1:A5)
Thank you Phuocam and NeedForExcel
both your formulas good for me.
Thanks. Have a nice day.
Glad I could help. Just 1 thing, My Solution has A1:A5<>0, and Phuocams soultion has A1:A5<>"". I think Phuocams is more precise than mine, so I would suggest you use that.
Oh...thanks for letting me know, may I know what's the difference?
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks