Tracking high point in a number sequence

[Greg1954]

I'm trying to create a reference cell that always shows the highest parameter from a daily update of numbers.

It is a stockmarket spreadsheet which tracks the movement in the share price. I need one cell to hold the highest price attained over a period of time.

I'm sure that if I simply put a new entry in a new cell each day, that I can have a reference that can looks at this and record the high point but over a long period of time, I'll end up with a spreadsheet hundreds of rows deep. If that's the only way to handle it, then so be it.

However, there must be a better way!

That is, I simply want to show:
1. The original buy price
2. The current price and
3. The highest price achieved to date (from the current price inputs, as the original is also the current price initially)

1 and 2 are inputs and 3 is to be triggered automatically from these inputs.

Can anybody help as I'm a bit of a novice?

Thanks.

 

[anno]

don't think i quite get what you mean, but have a look at the =MAX() function

 

[Chris Davison]

Hi Greg,

I'll bump this back to the top of the list for the VBA guys....

I can't see how this is achievable without a few lines of tecchie coding

now watch those replies come in.....(!)

 

[Jay Petrulis]

Hi Greg,

I don't quite follow you. Will, as anno says, MAX work for you?

If you are updating a stock portfolio each day, won't you have a huge dataset with hundreds of rows?

If you want the max for the last quarter (90 days), you could use something like (assume prices in column B:

=MAX(INDIRECT("B"&MATCH(9.9E+306,B:B,1)-89&":B"&MATCH(9.9E+306,B:B,1)))

average and min work the same way, for instance.

If you want to have cell three with the max price where you don't store history in a table, you can use
=IF(Cell2>Cell3,Cell2,Cell3)
and then paste the values (too much work)

You can use an event macro like the following...
--------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 2 _
Or Len(Target) = 0 Then Exit Sub

If Target.Value > Target.Offset(0, 1) Then _
Target.Offset(0, 1) = Target.Value
End Sub
--------------------

Place in the sheet module, not in a regular workbook module.

Bye,
Jay