Help Complicated formulae between columns

**Bohk** · 08-01-2009, 04:15 PM

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.

**martindwilson** · 08-01-2009, 04:27 PM

could you put that in a spread sheet and attach to post please

**Bohk** · 08-01-2009, 04:40 PM

Hi

Ok, thanks.

I modified it a bit to have something more relevant.

B.

**martindwilson** · 08-01-2009, 05:25 PM

in d2 dragged down
=SUMPRODUCT(--($F$2:$F$94>=B2),--($F$2:$F$94<=C2),(G2:G94))/SUMPRODUCT(--($F$2:$F$94>=B2),--($F$2:$F$94<=C2))
adjust ranges to suit but must all be of the same dimensions eg if f2:f1000,so must g2:g1000
you have excel 2007 so you can wrap an iferror around that to get rid of div0 errors
=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")

**Bohk** · 08-01-2009, 06:06 PM

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.

**Bohk** · 08-01-2009, 07:22 PM

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.

**martindwilson** · 08-01-2009, 07:25 PM

my fault missed fixing ref in g (it changes as you drag down otherwise)
should read
=iferror(SUMPRODUCT(--($F$2:$F$94>=B2),--($F$2:$F$94<=C2),($G$2:$G$94))/SUMPRODUCT(--($F$2:$F$94>=B2),--($F$2:$F$94<=C2)),"none in range")
the -- changes whats in the arrays
e.g
($F$2:$F$94>=B2)would produce false,false,true,true,true...........and so on for every row it doesnt match the criteria in b2
the -- converts that to 0,0,1,1,1.......
so say the value in b2 is a match it gives true ie 1
and the value in c2 ($F$2:$F$94>=c2) is a match that would also give true ie 1
the last value is taken from ($G$2:$G$94) ie whats in g2 i.e 26
so sumproduct is literally the sum of those values after you have multiplied them together
1 x1x26 =26
now take next row
assume that there is one match and one no match
true and a false
so you'd get 1 x 0 x 26 which is =0
add 0 to 26 still =26
but if it was true true
youd get 1x1 x26 again
add that to the row above =52
thus summing all values that meet the criteria.
if you leave of the last argument (g2:g94) as in the second part of the formula
/SUMPRODUCT(--($F$2:$F$94>=B2),--($F$2:$F$94<=C2))
the same rules aply but as there is no value to return for row g
1 x1 =1
1 x0 =0
adding those together gives a count of matching values
and average =sum/count