Index & Counta formula problem
 

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

Index & Counta formula problem
 

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
last entry even if their is a blank cell?
EXAMPLE:
b35 45
b36 50
b37 55
b38
b39

In above example formula returns 55 which is correct!

b35 45
b36
b37 55
b38
b39

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)

Thanks
Bob Leonard

.

Index & Counta formula problem
 

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)

"rleonard" wrote in 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
last entry even if their is a blank cell?
EXAMPLE:
b35 45
b36 50
b37 55
b38
b39

In above example formula returns 55 which is correct!

b35 45
b36
b37 55
b38
b39

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)

Thanks
Bob Leonard

Index & Counta formula problem
 

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


"rleonard" wrote in 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
last entry even if their is a blank cell?
EXAMPLE:
b35 45
b36 50
b37 55
b38
b39

In above example formula returns 55 which is correct!

b35 45
b36
b37 55
b38
b39

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)

Thanks
Bob Leonard

Index & Counta formula problem
 

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

"rleonard" wrote in 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
last entry even if their is a blank cell?
EXAMPLE:
b35 45
b36 50
b37 55
b38
b39

In above example formula returns 55 which is correct!

b35 45
b36
b37 55
b38
b39

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)

Thanks
Bob Leonard

Index & Counta formula problem
 

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)

"rleonard" wrote in message
...
Rob,

If all entries are blank, how can I change formula so it does not return
#N/A

Formula worked once I began entering entries in cells.

Instead of returing #N/A could it return "No Scores"

Thanks
Bob Leonard

Index & Counta formula problem
 

yet another solution:

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

Sal