Counting Higher Highs
Dear all,
I am trying to find a formula in excel that will allow me to count the number of higher highs or new highs in a data series. eg. 1,2,1,4,1,3,6,3, this series has three new highs ie numbers 2,4, and 6. Any ideas would be very much appreciated. Thanks. -- rpee |
Counting Higher Highs
If you've got your data in coulmn A, then a formula in column B, with B1
initialised to zero, and B2 showing =IF(A2<A1,B1+1,B1) [and copied down from there] might do the trick? -- David Biddulph "rpee" wrote in message ... Dear all, I am trying to find a formula in excel that will allow me to count the number of higher highs or new highs in a data series. eg. 1,2,1,4,1,3,6,3, this series has three new highs ie numbers 2,4, and 6. Any ideas would be very much appreciated. Thanks. -- rpee |
Counting Higher Highs
I realise that you probably were defining your "higher highs" differently,
so try replacing my formula below by =IF(A2MAX(A$1:A1),B1+1,B1) [In my formula below I'd been looking at your 2, 4, and 6 and seeing that these were maxima, after which the value of the numbers reduced again, but I guess that wasn't exactly what you were looking for.] -- David Biddulph "David Biddulph" wrote in message ... If you've got your data in coulmn A, then a formula in column B, with B1 initialised to zero, and B2 showing =IF(A2<A1,B1+1,B1) [and copied down from there] might do the trick? -- David Biddulph "rpee" wrote in message ... Dear all, I am trying to find a formula in excel that will allow me to count the number of higher highs or new highs in a data series. eg. 1,2,1,4,1,3,6,3, this series has three new highs ie numbers 2,4, and 6. Any ideas would be very much appreciated. Thanks. -- rpee |
Counting Higher Highs
Hi rpee,
David's formula is quite elegent, but I wondered if you only wanted the 3 highest highs, in which case you could do it using =LARGE Here's an example Data Highest 2nd Highest 3rd Highest 1 6 4 3 1 3 4 6 3 The formula in column 2 is =MAX(A2:A7), for the highest number, in column C it's =LARGE(A2:A7,2) and C contains =LARGE(A2:A7,3). The 2 and the 3 in the formulae indicate the 2nd and 3rd highest values. Hope this helps, Phil David Biddulph wrote: If you've got your data in coulmn A, then a formula in column B, with B1 initialised to zero, and B2 showing =IF(A2<A1,B1+1,B1) [and copied down from there] might do the trick? -- David Biddulph "rpee" wrote in message ... Dear all, I am trying to find a formula in excel that will allow me to count the number of higher highs or new highs in a data series. eg. 1,2,1,4,1,3,6,3, this series has three new highs ie numbers 2,4, and 6. Any ideas would be very much appreciated. Thanks. -- rpee |
All times are GMT +1. The time now is 04:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com