Formula that tells me if the temp has exceed a temp of 2 degrees for more than an hour.

Ive tried some options but cant get it to stick.

I have a some data around 5000 rows.Each row is date and time stamp of 15 minutes, every 15 minutes its takes a temperature reading. In column one is a date and time in row 2 is temp. i need a formula that tells me if the temp has exceed a temp of 2 degrees for more than an hour.

what is the best way to do this.


test data excel.pdf

Regards

Administrative Note:

Welcome to the forum.

Your thread title did not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

(Note: this change is not optional. As you are new here, I have done it for you today.)

Whilst I understand that you may be in a bit of a panic, don't tell us that it is URGENT, please. All your helpers here are volunteers giving freely of their own time: you haven't paid a penny for any assistance you get here, therefore expecting any form of preferential treatment, or seeming to, is inappropriate.

Thank you for your understanding. I have removed the urgency notice from your thread title.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

I would set one column to find if temp is 2 degrees or greater difference. If((B2-x)>=2,1,"") or something then in next column =If(Countif(Indirect("C"&Row()-4&":C"&Row()),1)=4,"1 Hour","")

Please Login or Register  to view this content.

Row() is a formula. are you just testing the temp in B?

You can eliminate column 3 then and use in the Count (instead of counting the ones) ">="&2

Please Login or Register  to view this content.

Do you really need the indirect() here??

In D4 copied down:

Formula:

Please Login or Register  to view this content.

I think this gives the same results.

And in col-H, it looks like you were trying to generate an up/down trend, so in H3 copied down:

Formula:

Please Login or Register  to view this content.