Highest Value

R

rwcs

New Member
Joined
May 10, 2002
Messages
5
I have a spreadsheet that tracks my investments. I would like to add cells that "remember" the "high water mark" obtained by various investments.

Given:
A1 curent Value
B1 Highest Value

How do I set up

IF A1 > B1 then B1 = A1

The circular reference errors are making me crazy.

Bob
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
On 2002-05-11 08:45, rwcs wrote:
I have a spreadsheet that tracks my investments. I would like to add cells that "remember" the "high water mark" obtained by various investments.

Given:
A1 curent Value
B1 Highest Value

How do I set up

IF A1 > B1 then B1 = A1

The circular reference errors are making me crazy.

Bob
Click to expand...

Are you computing/retrieving the current and the highest values or entering them manually in A1 and B1 respectively?
 
Upvote 0
The initial concept was that A1 would be a sum function (sum of all individual investments). But it would be nice to be able to apply this to investment elements, which would include manually entered values.
 
Upvote 0
I've looked at a bunch of functions:

Specificaly
If B1 is =MAX(A1) it performs no differently than =A1
IF B1 is =MAX(A1:B1) result is always Zero (0) (Same result =MAX(A1,B1))

Do you have another MAX statement that works?

Bob
 
Upvote 0
I was thinking of having the Max function pointing at the various investments . For example if all your investments were in row A then place MAX(A:A) in the B1 cell.
If they were in specific cells then =MAX(A1,A21,A33).
 
Upvote 0
On 2002-05-11 09:08, rwcs wrote:
The initial concept was that A1 would be a sum function (sum of all individual investments). But it would be nice to be able to apply this to investment elements, which would include manually entered values.
Click to expand...

I'd advise, if applicable, retrieving the current value in A1 and computing the highest value in B1. That would look like this:

In A1 enter:

=OFFSET(Investments!C1,MATCH(9.99999999999999E+307,Investments!C:C)-1,0,1,1)

In B1 enter:

=MAX(Investments!C:C)

The first formula retrieves last entered investment value in column C in sheet Investments.

The second formula computes the highest value from all values entered in column C in sheet Investments.

If you'd like to use the SUM function in A1, then:

In A1 enter:

=SUM(Investments!C:C)

In B1 enter:

=MAX(A1,MAX(Investments!C:C))

although I don't see what the result of the latter would possibly mean.

If you insist entering both the Current Value and the Highest Value in A1 and B1, you'll need VBA code, which the VBA army around this board can give you.

Aladin
 
Upvote 0
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
 
Upvote 0
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
Click to expand...

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
 
Upvote 0
Here is a bruit force macro method:

Sub Auto_Open()
x = Worksheets("Sheet1").Range("A1").Value
y = Worksheets("Sheet1").Range("B1").Value
If x > y Then y = x
Worksheets("Sheet1").Range("B1").Value = y
End Sub

Is there an easier way?
Bob
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Capturing Highest Historical Rank in a Continuously Updated Worksheet
2
Replies
11
Views
330
Mz309Lord
M
D
  • Question
Check if duplicate projects with dates exist
Replies
2
Views
95
dubyarly
D
P
  • Question
Named ranges and dashboard
Replies
5
Views
134
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Solved
Rounding time
2
Replies
18
Views
566
felixstraube
F
A
  • Question
Multiple row/cell realtime count
Replies
2
Views
281
akaseto
A

Forum statistics

Threads
1,214,627
Messages
6,120,610
Members
448,973
Latest member
ChristineC

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top