Tracking high point in a number sequence
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Tracking high point in a number sequence

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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



    :: Pharma Z - Family drugstore ::

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com