Counting Occurrences

[RiaanDeysel]

Hi,

I have a set of temperature data that I need to analyse and determine the number of excursions above a certain temperature.
The problem is my data is in 2 minute intervals and I do not want to count consecutive excursions separately but rather as a single excursion. For example:

1.JPG

If I use the countif function it counts the number of times a temperature is above the certain limit, in this case 550 as seen in row 16. However I want it to count the number of times the temperature exceeded the limit but returned to below the limit, as seen in row 17.

Hope someone will be able to assist me.

Thanks,
RiaanDeysel

[ranman256]

pivot table.
record a macro to run it and count Col C.
run it after you update the data.

[Tony Valko]

How do you get the result of 14? There are 6 values >550.

It looks like you want to count consecutive cells that are >550 as a single instance based on the results in column C.

If that's the case try this array formula**:

=SUM(IF(FREQUENCY(IF(B2:B15>550,ROW(B2:B15)),IF(B2:B15<=550,ROW(B2:B15)))>1,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[RiaanDeysel]

Hi Tony,

Sorry I only saw that mistake after you mentioned it, looks like I used count and not countif for that example.

Thanks for the advise will definitely try the array formula and give feedback if it works.

[RiaanDeysel]

Tony,

So I have tried using your array formula and it seems to be working. However, it only counts occurrences if there are two or more consecutive cells >550 but not single cells >550
I changed some of the values to show more excursions for the example:

2.JPG

Any advice?

[Ipsenh]

Ok, this is the first time i post an answer, so please bear over with me if I do not follow the correct procedure, but I have checked the rules and hope I comply

You can use this macro:

Please Login or Register  to view this content.

This will return the number 6 in cell C16 and the number 2 in C17.

Another way without using Macro's:
Insert this formula in all 14 lines in column C (C2:C15) with cell C2 as the example here:

Please Login or Register  to view this content.

And in all cells in column D starting with D3 (D3:D15) you insert this with cell D3 as the example hereunder:

Please Login or Register  to view this content.

In cell C16 you then write:

Please Login or Register  to view this content.

And in C17:

Please Login or Register  to view this content.

This will give you the same result as the Macro above.

Hope this will work for you.

BR
Ipsenh
(A.K.A. Excelipsen)

[RiaanDeysel]

Hi Ipsenh,

Simple and easy, thank you it works!

Regards,
RiaanDeysel

[Tony Valko]

I based the formula on the data and expected results in the first post.

If you want to count every streak as a single instance then use this version:

=SUM(IF(FREQUENCY(IF(B2:B15>550,ROW(B2:B15)),IF(B2:B15<=550,ROW(B2:B15))),1))

Still array entered.