Find number of instances data is with with a range

[TimTDP]

In Excel 2010, I need to analyse data. The data is time stamped, i.e column A contains data in the format dd/mm/yyyy hh:mm:ss. Column B contains values

I want to look at the data in column B and determine:
* how many times data exceeded a specific value.
* the time period the data exceeded the specific value. i,e The data exceed the specific value at this time (Column A) and returned >= to the specific at at this time (Column A), therefore it was out of range for "X" time

How would I do this?

[ChemistB]

Ideally, if you post an example spreadsheet with dummy data and examples of what it looks like and what you want returned, this becomes much easier and you'll get more specific recommendations.

"How many times"
=COUNTIF(B:B, "> 0.5")
Not sure about how your data is set up to solve the second part.

[TimTDP]

Thanks for the answer to part 1. This is perfect

I have attached an example spreadsheet with dummy data

[ChemistB]

Okay, here's what I came up with for your second formula. It puts it next to the first excursion rather than the last one (as per your example). Enter this into E11 dragged down.

=IF(AND(D11>=3, D10<3),TEXT((INDEX(C11:$C$29,MATCH(3,D11:$D$29,-1)+1)-C11) * 60 * 24,"#")&" minutes","")

Does that work for you?

[TimTDP]

Brilliant. Thank you

[TimTDP]

Hi ChemistB

I hope you are still monitoring this post!

I have found a minor glitch in your solution and I hope you can fix it!

In the attached spreadsheet the answers appear correct. But change the value in cell D11 to 3 and the results are incorrect.

Can you please have a look?

Much appreciated

[ChemistB]

The culprit is the MATCH portion. Change the 3 to a 2.99. You want it to keep searching until it finds a value greater than or equal to 2.99.

[TimTDP]

Next, and hopefully last problem!!

If the time, Column C, goes over midnight, and data is above limit I get an error. The formulae returns a negative number

[ChemistB]

Try this in F11 of your example

=IF(AND(D11>=3, D10<3),TEXT((MOD(INDEX(C11:$C$40,MATCH(2.99,D11:$D$40,-1)+1)-C11,1)) * 60 * 24,"#")&" minutes","")

The MOD Function, in this case, allows you to get the absolute value of the difference of the times.

[TimTDP]

Thank you very, very much