Percentage of new highs (simple array please)

[chipps24]

Hi, I'm sure this will be pretty simple for the advanced users on this forum, but thanks vm in advance.

I would like an array formula that calculates the percentage of occurrences that a series of data makes a new high.

Column A - data (it rises and falls)
Column B - intermediate step to calculate when a new high is reached. Note the definition of a new high includes the entire series.

D1 - the answer required.

As always your help is much appreciated.

Thanks vm.


Excel 2013 32 bit

	A	B	C	D
1	1			43%
2	2	h
3	3	h
4	4	h
5	5	h
6	4
7	3
8	2
9	3
10	4
11	5
12	6	h
13	7	h
14	8	h
15	9	h
16	8
17	9
18	10	h
19	9
20	8
21	7

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D1 =COUNTIF(B1:B21,"h")/COUNT(A1:A21) B2 =IF(A2>MAX($A$1:A1),"h","") B3 =IF(A3>MAX($A$1:A2),"h","") B4 =IF(A4>MAX($A$1:A3),"h","") B5 =IF(A5>MAX($A$1:A4),"h","") B6 =IF(A6>MAX($A$1:A5),"h","") B7 =IF(A7>MAX($A$1:A6),"h","") B8 =IF(A8>MAX($A$1:A7),"h","") B9 =IF(A9>MAX($A$1:A8),"h","") B10 =IF(A10>MAX($A$1:A9),"h","") B11 =IF(A11>MAX($A$1:A10),"h","") B12 =IF(A12>MAX($A$1:A11),"h","") B13 =IF(A13>MAX($A$1:A12),"h","") B14 =IF(A14>MAX($A$1:A13),"h","") B15 =IF(A15>MAX($A$1:A14),"h","") B16 =IF(A16>MAX($A$1:A15),"h","") B17 =IF(A17>MAX($A$1:A16),"h","") B18 =IF(A18>MAX($A$1:A17),"h","") B19 =IF(A19>MAX($A$1:A18),"h","") B20 =IF(A20>MAX($A$1:A19),"h","") B21 =IF(A21>MAX($A$1:A20),"h","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[mancubus]

hi chipps

what is wrong with Column B as a helper column?

 

[Eric W]

Maybe something like this?

	A	B	C	D
1	1			43%
2	2	h
3	3	h
4	4	h
5	5	h
6	4
7	3
8	2
9	3
10	4
11	5
12	6	h
13	7	h
14	8	h
15	9	h
16	8
17	9
18	10	h
19	9
20	8
21	7
22

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula D1 {=SUM((--(A2:A21>SUBTOTAL(4,OFFSET(A1,0,0,ROW(A1:A20))))))/21} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[chipps24]

Thanks very much Eric. Is there any reason why the formula works in the very basic 'example' spreadsheet created to communicate on this forum, and not in my more complex workbook that contains the real actual data? ie, is there some sort of setting I need to apply? The formula works perfectly in the basic workbook, but not in my main book. I have ensured that the curly brackets are working too, and that the cell references are aligned perfectly as per your example formula.


Mancubus, I have a very workbook with many similar calculations to perform, so I need this to keep my workbook as efficient as possible.


thanks vm

 

[Eric W]

I can't see any reason why it shouldn't work on your full data. Did you change the cell references?

=SUM((--(A2:A21>SUBTOTAL(4,OFFSET(A1,0,0,ROW(A1:A20)-ROW(A1)+1)))))/21


​You used the COUNT function to get the 21 while I just put in the length of the range.

I added the -ROW(A1)+1 in case your range doesn't start in row 1.

 

[chipps24]

Arr fantastic thanks Eric, that's done the trick!

Much appreciated!