Index & Counta formula problem

[Guest]

My formula returns last value in column of numbers. If none of the cells are blank between first entry and last entry,th
formula works. But if one cell is blank formula returns last entry before blank cell. How can i modify formula to displa
last entry even if their is a blank cell
EXAMPLE
b35 4
b36 5
b37 5
b3
b3

In above example formula returns 55 which is correct

b35 4
b36
b37 5
b3
b3

In above example returns 45, I need it to return 55 as that is last entry

Following is my formula

=INDEX(B35:B39,COUNTA(B35:B39),1

Thank
Bob Leonar

 

[Guest]

it's not the cleanest way to do it, but the only way i know
how to do this is if you know what the maximum number of
rows you have is. try this:

= Range("B50").End(xlUP).Value

where the "B50" cell is further down than the rows you
have. how this works is that you give it a cell to start at
and excel works its way up the culumn from the bottom cell
you provide until it finds the first non-empty cell. hope
that helps

-----Original Message-----
My formula returns last value in column of numbers. If

none of the cells are blank between first entry and last
entry,the

formula works. But if one cell is blank formula returns

last entry before blank cell. How can i modify formula to
display

 

[Bob Phillips]

Bob,

Try this

=INDEX(B:B,MAX(IF(B35:B39<>"",ROW(35:39))))

it's an array formula, so commit with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

My formula returns last value in column of numbers. If none of the cells

are blank between first entry and last entry,the

formula works. But if one cell is blank formula returns last entry before

blank cell. How can i modify formula to display

 

[Rob van Gelder]

One way might be to use MATCH

Assuming all values in B are greater than 0

=INDEX(B35:B39, MATCH(0, B35:B39, -1), 1)

--
Rob van Gelder - http://www.vangelder.co.nz/excel

My formula returns last value in column of numbers. If none of the cells

are blank between first entry and last entry,the

formula works. But if one cell is blank formula returns last entry before

blank cell. How can i modify formula to display

 

[Aladin Akyurek]

=LOOKUP(9.99999999999999E+307,B:B)

My formula returns last value in column of numbers. If none of the cells

are blank between first entry and last entry,the

formula works. But if one cell is blank formula returns last entry before

blank cell. How can i modify formula to display

 

[Bob Phillips]

Bob

Try

=IF(ISERROR(MATCH(,B35:B39,-1)),"No Sources",INDEX(B35:B39, MATCH(0,
B35:B39, -1), 1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[cyclingsal]

yet another solution:

Sub checklv()
With Worksheets("sheet1")
lv = .Cells(Rows.Count, "A").End(xlUp).Value
MsgBox "lastvalue = " & lv
End With
End Sub

Sal