Finding last cell

[Jim]

How would I reflect, in say in sheet2, the last amount in col B, before the
% shown
in in col B, which will change as i add new dates?
In my old 123 days to find this cell i would record a macro like goto col B,
end down,
down,end up. Not sure how to accomplise this in Excel 2003.

Thanks,
Jim


A B C D
1 2/7/07 86,051.28 1,784.53 2.12%
2 2/9/07 85,504.54 1,237.79 1.47%
3 2/13/07 85,910.57 1,643.82 1.95%
4 2/14/07 86,963.25 2,696.50 3.20%



YTD 3.20%

 

[Guest]

=INDIRECT("B"&SUMPRODUCT(MAX((ROW(B1:B1000))*(B1:B1000<>""))))

 

[Bob Phillips]

You don't need a macro

=LOOKUP(2,1/(B1:B100<>""),B1:B100)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim]

This function will give me the value in the last cell, which is 3.20%. I'm
trying to get the value in the last row before the last row.
(sounds kind of dumb) 86,963.25
Thanks Jim

 

[Jim]

This function will give me the value in the last cell, which is 3.20%. I'm
trying to get the value in the last row before the last row & not necessarly
the largest amount.
86,963.25
Thanks Jim

 

[Guest]

Very Strange...

On my computer: WinXP / Excel 2002
the formula does return
86,963.25


This formula has been very reliable and works about 99.99847412109370% of
the time.

 

[Jim]

Thanks for all your help. I just rearranged my worksheet so the % will be in
a different col.
Thanks Again
Jim

 

[Dave Peterson]

But you didn't put the YTD stuff under that table.

 

[Dave Peterson]

First, you may want to consider putting the YTD info in row 1--then the other
formulas will work ok.

And if you use Windows|Freeze Panes, you could make it so that it (and the
headers) are always visible.

But if your dates are in nice order (ascending), then you could use a formula
like:

=INDEX(B:B,MATCH(MAX(A:A),A:A,0))

But you can't have any numbers bigger than that last date in column A.

 

[Jim]

This is what I'm actually working with:
A B C
2 84,926.02
3 2/7/07 95,000.00 1,784.53
4 2/9/07 85,504.54 1,237.79
5 2/13/07 85,910.57 1,643.82
6 2/14/07 86,963.25 2,696.50
7 2/16/07 87,114.37 2,847.62

YTD 3.35% ( =+C7/B2 formula in B9)
ANNUAL 26.04% (=+B9/47*365 formula in B10)

The YTD & ANNUAL "formula I change whenever I update my sheet.
Just trying to find a better way & one is to restructure my spreadsheet.

Jim (seem to have lost some brain cells the last 10 years)

 

[Dave Peterson]

How about using two additional columns E:F and put those equivalent formulas in
those cells.

And then you could actually look back to see how things are progressing.

 

[Jim]

Actually, thanks to all involved, I played around with the functions given
me & changed the YTD & ANNUAL functions, which I was updating manually to:
=INDEX(C:C,MATCH(MAX(B:B),B:B,0))/$B$8
=C31/(INDEX(A6:A400,MATCH(MAX(A6:A400),A6:A400,0))-$A$5)*365 (where $A$5 is
12/31/06)

I already have 14 columns across (tracing mutual funds) & think I'll take
your suggestion & move the Ytd, etc to another column.
Thanks again for all the help.

Jim

 

[Guest]

Maybe you could modifiy Bob's formula to do that. This appears to work okay:

=INDEX(B:B,LOOKUP(2,1/(B1:B100<>""),ROW(B1:B100))-1)

 

[Jim]

Thanks, I've got everything working now, just dont know why.
I ve been given the following but not sure what the (2,1 does in #1 & #4,
nor the ROW -1 does.
More reading & playing to do

=LOOKUP(2,1/(B1:B100<>""),B1:B100)
=INDIRECT("B"&SUMPRODUCT(MAX((ROW(B1:B1000))*(B1:B1000<>""))))
=INDEX(B:B,MATCH(MAX(A:A),A:A,0))
=INDEX(B:B,LOOKUP(2,1/(B1:B100<>""),ROW(B1:B100))-1)

 

[Guest]

I'll try to explain further the small changes I did w/Bob's formula (but
check my comments at the end - I would probably move the YTD formulas and
stick w/Bob's original formula).

=INDEX(B:B,LOOKUP(2,1/(B1:B8<>""),ROW(B1:B8))-1)

Assume you have the following data in column B:

B
1 X
2 58
3 4
4 <empty>
5 Joe
6 765
7 <empty>
8 <empty>

Starting in the middle of the formula, (B1:B8<>"") will return TRUE/FALSE
depending on whether or not the cell is empty. Excel actually stores TRUE as
1 and FALSE as 0, so 1/(B1:B8<>"") will yield

1
1
1
#DIV/0
1
1
#DIV/0
#DIV/0

LOOKUP(2,1/(B1:B8<>""),ROW(B1:B8), will try to match 2 to the above values
(but if it does not find it, it will return the largest value that is smaller
than what you are trying to look up -according to Excel help) and will return
the corresponding value from the array created by ROW(B1:B8).

In actual practice, as long as the value you are trying to look up is
greater than all of the values in your lookup vector - it appears Lookup will
return the last value (based on what I've seen).

So, imagine a table that looks like:

1 1
1 2
1 3
#DIV/0 4
1 5
1 6
#DIV/0 7
#DIV/0 8

So Lookup returns 6 and if we substitute that into the original formula you
have
=INDEX(B:B,6-1)
which will return the value in the 5th row in column B - which hopefully is
not blank or empty. So if you have blank/empty cells in between your data
there could be problems (which I did not think of until now).

Your best bet is probably to move the YTD formulas to the top of column B
(or to another column, which I think you said you've already done) and use
Bob's original formula to get the last value in column B.

 

[Jim]

I want to thank you very much for the extensive explanation of the formula.
Jim