Highest Value

[rwcs]

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

[Aladin Akyurek]

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

Are you computing/retrieving the current and the highest values or entering them manually in A1 and B1 respectively?

[rwcs]

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.

[Nimrod]

Have you looked at using the Max function in B1 ?

[rwcs]

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

[Nimrod]

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).

[Aladin Akyurek]

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.

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

[rwcs]

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

[Aladin Akyurek]

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:

Microsoft Excel - InvestmentsAdm.xls
File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
A1		=	Day
*	A	B	C	D
1	Day	Value	*	*
2	1	:alert('100')>$100.00	*	*
3	2	:alert('105')>$105.00	*	*
4	3	:alert('103')>$103.00	*	*
5	*	*	*	*
6	*	*	*	*
Investments

You can see the formula of cells only click each above hyperlinks

The above image was automatically written by excel VBA. If you want this code, click here and I'll email the file to you.

Microsoft Excel - InvestmentsAdm.xls
File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
A1		=	Current Value
*	A	B	C	D
1	Current Value	:alert('=OFFSET(Investments!$B$1,MATCH(9.99999999999999E+307,Investments!B:B)-1,0,1,1)')>$103.00	*	*
2	Highest Value	:alert('=MAX(Investments!B:B)')>$105.00	*	*
3	*	*	*	*
4	*	*	*	*
Overview

You can see the formula of cells only click each above hyperlinks

The above image was automatically written by excel VBA. If you want this code, click here and I'll email the file to you.


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 ]

[rwcs]

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