Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Switzerland
    Posts
    90
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help needed creating an array formula (2003)

    Folks

    I'm looking for some help...

    I am having a range with DATES and PRICES (say a history of stock
    prices).

    I'm trying to build a formula that would return the maximum number of
    consecutive (date wise, the range is sorted) records with unchanged PRICES, ie

    (...)
    21/02/2000 91.36
    22/02/2000 91.38
    23/02/2000 91.44
    24/02/2000 91.22
    25/02/2000 91.36
    26/02/2000 91.36
    27/02/2000 91.36
    28/02/2000 91.37
    29/02/2000 94.17
    01/03/2000 94.17 <- 1
    02/03/2000 94.17 <- 2
    03/03/2000 94.17 <- 3
    04/03/2000 94.17 <- 4
    05/03/2000 94.17 <- 5
    06/03/2000 94.17 <- 6
    07/03/2000 94.11
    08/03/2000 94.12
    (...)

    Would return 6 (6 data points with the same price)

    I feel that this should be possible with an array formula but it seems it's a
    little bit too complex for my level of expertise...

    Any help welcome

    -AlexT

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help needed creating an array formula (2003)

    If this can be done in one array formula, one of the Excel gurus will post it. I have attached a simpler approach using a column with intermediate results. This column can be hidden if desired.

Posting Permissions

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