On 2002-05-11 09:57, rwcs wrote:
I have investment "A" lets say the value today is $100. Tomorrow I got to my investment site and see the value has risen to $105, so I record that in my spreadsheet. The day after my investment has dropped to $103, so I record the value in my spreadsheet. How do I know on day 3 what the highest value of this investment has been in the past. What I would like to see:
Day 1: A1 [$100] B1 [$100]
Day 2: A1 [$105] B1 [$105]
Day 3: A1 [$103] B1 [$105]
Is this possible?
Thanks
Bob
Bob,
The above scenario doesn't have any memory. How do you suppose to know the highest value at the
nth day? I can't, can you?
What I'd suggest is illustrated with sheets that follow:<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=5><FONT COLOR=WHITE>
Microsoft Excel - InvestmentsAdm.xls</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=5>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Tool(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>A1</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >
=</TD><TD COLSPAN=2 BGCOLOR=White>Day</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
D</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
1</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>
Day</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>
Value</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
2</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>1</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('100')><FONT FACE=MS P???? COLOR=#000000>$100.00</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>2</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('105')><FONT FACE=MS P???? COLOR=#000000>$105.00</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>3</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('103')><FONT FACE=MS P???? COLOR=#000000>$103.00</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
6</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=5><U>Investments</U></TD></TR></TABLE><FONT COLOR=#339966>You can see the formula of cells only click each above hyperlinks</FONT><FONT COLOR=blue SIZE=1>The above image was automatically written by excel VBA.</FONT><FONT COLOR=blue SIZE=1>If you want this code,<A HREF=mailto:corosuke@chan.co.jp>click here</A> and I'll email the file to you.</FONT></CENTER><CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=5><FONT COLOR=WHITE>
Microsoft Excel - InvestmentsAdm.xls</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=5>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Tool(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>A1</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >
=</TD><TD COLSPAN=2 BGCOLOR=White>Current Value</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
D</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
1</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>
Current Value</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=OFFSET(Investments!$B$1,MATCH(9.99999999999999E+307,Investments!B:B)-1,0,1,1)')><FONT FACE=MS P???? COLOR=#000000>$103.00</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
2</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>
Highest Value</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MAX(Investments!B:B)')><FONT FACE=MS P???? COLOR=#000000>$105.00</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
3</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
4</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=5><U>Overview</U></TD></TR></TABLE><FONT COLOR=#339966>You can see the formula of cells only click each above hyperlinks</FONT><FONT COLOR=blue SIZE=1>The above image was automatically written by excel VBA.</FONT><FONT COLOR=blue SIZE=1>If you want this code,<A HREF=mailto:corosuke@chan.co.jp>click here</A> and I'll email the file to you.</FONT></CENTER>
The Overview sheet will inform you automatically about the state of your investments as long as you enter the daily values in the sheet Investments which is in the same workbook as the sheet Overview.
What do you think?
This message was edited by Aladin Akyurek on 2002-05-11 10:19