Data extraction

Hi,

I am interested to know two things from my data

1) 'Nr of rows' when the temp was between 80 - 90 degress (column B); after the temp reahced 80 degrees for the first time; for e.g for part 352 it is '25 rows' and for part 353 it is '21 rows'

2) For each part i want to know the Nr of rows from start until the temp begin to drop more than 3 degress; for e.g for part nr 352 it is '164 rows' and for part nr 353 it is '169 rows'

Can any one tell what is the best way to do this. I have hundreds of parts so doing it manually would be not feasible.

many thanks

Well this formula should work for the first part, where the part number it's matching is in cell A2 ...

=SUMPRODUCT(--($A$2:$A$705=A2),--($B$2:$B$705>=80),--($B$2:$B$705<=90),--(ROW($B$2:$B$705)>MIN(INDEX(ROW($A$2:$A$705)+(999*($A$2:$A$705<>A2))+(999*($B$2:$B$705<80)),0))))

I'll have a think about the 2nd part.

OK, this formula indicates the number of rows until the temerature starts to drop, but not quite sure what you mean by "more than 3 degrees" - none of your data contains a row which is 3 or more degrees higher than the following row:

=MIN(INDEX(ROW($A$2:$A$705)+(($A$2:$A$705<>A2)*9999)+(($B$2:$B$705<=($B$3:$B$706))*9999),0))-MIN(INDEX(ROW($A$2:$A$705)+(($A$2:$A$705<>A2)*9999),0))+1

ok thanks a lot, i tried the first forumla and it give me correct answer on the sample table i sent you but when i copied the data on the entire data set; i get incorrect results. the entire dataset contains 272983 rows, what is the purpose of '999*' in the formula? do i need to change it to a bigger number if my rows are much higher.

when i say temp drop of 3 degrees or more is that becacuse sometime the temp drop for 1 or 2 degress and than raise again and then i dont see the 'real temp drop' down i am interested to see. i will try to use the second formula as well and return if any questions but again thanks a loooot for saving my time

Yes, the "999" just needs to be a value bigger than the last row number containing data.

I see what you mean by the temperature drop, I'm just not sure how it would translate into a formula. Could we work on the row where the temperature drops to 3 degrees below the maximum recorded temperature, or would that not give you what you're after?

I tried with a value higher than the last row but i still got incorrect result. for eg. for the first 'part' the correct answer is 28 but the formula gives 38. I have changed the the 'part nr' column with 'date' because some part nrs have same value but different dates so i thought it would be better to use dates instead. Also when i copied down the formula i still get the results for all the parts. Any ideas why it is happening???

I think it would be fine to ignore the 3 degree temp drop as in most of the parts the temp does not drop and rise