Help Complicated formulae between columns

Hi

I need help... I have to calculate something on my data, and I dont know how to do it. Maybe it's very simple, or maybe it's not possible on excel.
But I need to know! because otherwise, by hand, it's gonna take me a loooong time...

I need to get the average of the temperature between 2 times, "timemate" and "timeend", for each row. The temperature has been measured every ten seconds.


My datasheet (a sample) :

2 PARTS =

#### First part

A B C D
Ind timemate timeend tempmating
1 10:58:06 11:01:18 ?
2 10:54:42 10:58:47 ?
3 10:54:00 10:57:55 ?
4 NA NA ?
5 10:55:00 10:59:19 ?
6 10:58:23 11:01:49 ?
7 10:56:00 11:01:35 ?
8 10:59:40 11:03:41 ?
9 11:04:29 11:09:25 ?
10 11:00:50 11:07:23 ?
11 11:05:00 11:08:37 ?
12 11:06:56 11:11:11 ?
13 11:05:43 11:11:38 ?
14 11:08:11 11:12:30 ?
15 11:08:24 11:11:21 ?
16 11:09:47 11:13:55 ?
17 11:09:47 11:13:26 ?
18 11:17:35 11:27:42 ?
19 NA NA ?
20 11:09:47 11:14:04 ?

##### 2nd part
E F
Time temp
10:51:53 26
10:52:03 26
10:52:13 26
10:52:23 26
10:52:33 26
10:52:43 26
10:52:53 26
10:53:03 26
10:53:13 26
10:53:23 26
10:53:33 26
10:53:43 26
10:53:53 26
10:54:03 26
10:54:13 26
10:54:23 26
10:54:33 26
10:54:43 26
10:54:53 26
10:55:03 26
10:55:13 26
10:55:23 26
10:55:33 26
10:55:43 26
10:55:53 26
10:56:03 26
10:56:13 26
10:56:23 26
10:56:33 26
10:56:43 26
10:56:53 26
10:57:03 26
10:57:13 26
10:57:23 26
10:57:33 26
10:57:43 26
10:57:53 26
10:58:03 26
10:58:13 26
10:58:23 26
10:58:33 26
10:58:43 26
10:58:53 26
10:59:03 26
10:59:13 26
10:59:23 26
10:59:33 26
10:59:43 26
10:59:53 26
11:00:03 26
11:00:13 26
11:00:23 26
11:00:33 26
11:00:43 26
11:00:53 26
11:01:03 26
11:01:13 26
11:01:23 26
11:01:33 26
11:01:43 26
(ok i know that here the temp doesnt change... but its not always the case!)
###################################################

I need to have the average temperature corresponding to the time between timecop and timeend.
For the first answer, i need to get the averaged temperature of what's bold in the second part...

I dont know if it's really clear. But help me please! Im desperate...

Thanks

B.

Hi

Ok, thanks.

I modified it a bit to have something more relevant.

B.

Hi

Thank you for your interest in my request.
I tried to apply your formula, but I dont find the good values, but it's close (weird), when i just apply the AVERAGE formula to the corresponding cells.
Im gonna try to figure that out.

I joined the sheet.

What is the "--" in the formula? (I know, I suck... But I better on R!)

Cheers,

B.

Hi again

Actually, i just had to change ($G$2:$G$94) in your formula.

=iferror(SUMPRODUCT(--($F$2:$F$94>=B2),--($F$2:$F$94<=C2),(G2:G94))/SUMPRODUCT(--($F$2:$F$94>=B2),--($F$2:$F$94<=C2)),"none in range")

So, now it's working!

Thank you so much!!!!!!! (I think right now i could be in love with you )

B.

Ok, i get it.
Thank you, really. It saved me a lot of time, and it's probablye gonna be useful for my future datasets.
Really appreciate it.