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%

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

--
Gary''s Student
gsnu200706


"Jim" wrote:

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%

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" wrote in message
...
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%

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

"Bob Phillips" wrote in message
...
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" wrote in message
...
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%

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

"Gary''s Student" wrote in message
...
=INDIRECT("B"&SUMPRODUCT(MAX((ROW(B1:B1000))*(B1:B 1000<""))))

--
Gary''s Student
gsnu200706


"Jim" wrote:

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%

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.

--
Gary''s Student
gsnu200706


"Jim" wrote:

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

"Gary''s Student" wrote in message
...
=INDIRECT("B"&SUMPRODUCT(MAX((ROW(B1:B1000))*(B1:B 1000<""))))

--
Gary''s Student
gsnu200706


"Jim" wrote:

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%

Thanks for all your help. I just rearranged my worksheet so the % will be in
a different col.
Thanks Again
Jim
"Gary''s Student" wrote in message
...
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.

--
Gary''s Student
gsnu200706


"Jim" wrote:

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

"Gary''s Student" wrote in
message
...
=INDIRECT("B"&SUMPRODUCT(MAX((ROW(B1:B1000))*(B1:B 1000<""))))

--
Gary''s Student
gsnu200706


"Jim" wrote:

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%

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

Gary''s Student wrote:

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.

--
Gary''s Student
gsnu200706

"Jim" wrote:

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

"Gary''s Student" wrote in message
...
=INDIRECT("B"&SUMPRODUCT(MAX((ROW(B1:B1000))*(B1:B 1000<""))))

--
Gary''s Student
gsnu200706


"Jim" wrote:

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%







--

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 wrote:

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%

--

Dave Peterson

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" wrote in message
...
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 wrote:

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%

--

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 wrote:

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" wrote in message
...
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 wrote:

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%

--

Dave Peterson

--

Dave Peterson

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


"Dave Peterson" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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%

--

Dave Peterson

--

Dave Peterson

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" wrote:

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

"Bob Phillips" wrote in message
...
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" wrote in message
...
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%

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:B 1000<""))))
=INDEX(B:B,MATCH(MAX(A:A),A:A,0))
=INDEX(B:B,LOOKUP(2,1/(B1:B100<""),ROW(B1:B100))-1)



"JMB" wrote in message
...
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" wrote:

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

"Bob Phillips" wrote in message
...
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" wrote in message
...
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%

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" wrote:

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:B 1000<""))))
=INDEX(B:B,MATCH(MAX(A:A),A:A,0))
=INDEX(B:B,LOOKUP(2,1/(B1:B100<""),ROW(B1:B100))-1)



"JMB" wrote in message
...
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" wrote:

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

"Bob Phillips" wrote in message
...
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" wrote in message
...
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%

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

"JMB" wrote in message
...
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" wrote:

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:B 1000<""))))
=INDEX(B:B,MATCH(MAX(A:A),A:A,0))
=INDEX(B:B,LOOKUP(2,1/(B1:B100<""),ROW(B1:B100))-1)



"JMB" wrote in message
...
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" wrote:

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

"Bob Phillips" wrote in message
...
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" wrote in message
...
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%