Help needed creating an array formula (2003)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote