Calculating variance, sum, average for range of rows during a specific time interval???

[yyl05]

Hello all,

I have a huge csv file with a few hundred thousand rows. I would like to perform statistical calculations on it and also obtain the first row value, for each time interval.

Example for every 300seconds, I would like to calculate the variance of the total number of rows within the 1st 300seconds, 2nd 300 seconds, 3rd 300 seconds and so on.... So the total number of rows would vary with each 300 seconds interval.

I would also like to get the value of a specific column in the First Row for each 300 second interval.
Example during the 1st 300 seconds, I would like to get the value of the 1st row during the time interval, for the next 300seconds I would like to get the value of the 1st row in the time interval again.

I have a time column T, which is all in seconds, so maybe I could calculate for every T=T+300?

Any suggestions on how and steps to do it??

Thanks a million!!!

 

[thisoldman]

I assume you have a column of elapsed time sorted in ascending order. Does this work for you?

Book1
	A	B	C	D	E	F	G	H
1	Seconds	Reading		Interval	First Rdng	Mean	Variance	Std. Dev.
2	0	5		300	5	4.333	4.556	2.134
3	66	2		600	11	14.000	5.600	2.366
4	118	7		900	106	104.600	6.640	2.577
5	185	2
6	242	3
7	294	7
8	344	11
9	407	13
10	460	13
11	525	18
12	588	15
13	633	106
14	700	107
15	751	101
16	796	107
17	858	102
18	917	1111
Sheet1

Cell Formulas
Range	Formula
E2	=B2
E3	=INDEX(B:B, MATCH($D2, A:A) + 1)
F2	=AVERAGE($B$2:INDEX(B:B, MATCH($D2, A:A)))
F3	=AVERAGE(INDEX(B:B, MATCH($D2, A:A) + 1):INDEX(B:B,MATCH($D3, A:A)))
G2	=VAR.P($B$2:INDEX(B:B, MATCH($D2, A:A)))
G3	=VAR.P(INDEX(B:B, MATCH($D2, A:A) + 1):INDEX(B:B,MATCH($D3, A:A)))
H2	=STDEV.P($B$2:INDEX(B:B, MATCH($D2, A:A)))
H3	=STDEV.P(INDEX(B:B, MATCH($D2, A:A) + 1):INDEX(B:B,MATCH($D3, A:A)))

 

[yyl05]

This is what I am looking for, thank you so much!!

Also, is it possible to do all these calculations for a specific column value?
e.g for tcp.stream there are values =0,1,2,3,.....99999. How can I perform these formulas with tcp.stream==1 and so on?

I would like to perform the same calculations for the same 300seconds interval for when tcp.stream==1,2,3.....

Thank you so much for your help

 

[thisoldman]

I wonder if this could be done with a pivot table. I came up with this.

The duplicated time stamps and the blank cells produced some frowns. Why can't data be tidy? I used helper columns: one for the count of observations within an interval, two to hold MATCH formulas.

I used SUMPRODUCT formulas to calculate the mean and the variance over an interval for the tcp.stream; this may make sheet recalculations slow. I could not figure out a way to make AVERAGEIF or AVERAGEIFS work to determine the mean for a particular stream within an interval.

I went to 400,000 rows for the SUMPRODUCT and the COUNTIFS formulas—tighten or loosen those ranges as you see fit.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Seconds	tcp.stream	Reading		Interval_end	First Rdng	Mean	Variance	Std. Dev.
2	1		5		300	5	5.000	4.750	2.179
3	66		2		600	11	14.000	5.600	2.366
4	118	1	7		900	106	104.600	6.640	2.577
5	185	2	2
6	242	1	3
7	294	1	7		TCP to Match	2
8	300	1	7
9	300		7		Interval_end	First Rdng	Mean	Variance	Std. Dev.	Count	match.intvl	match.TCP
10	344	1	11		300	2	2.000	0.000	0.000	1	9	4
11	407	2	13		600	13	15.500	6.250	2.500	2	14	2
12	460	1	13		900	102	102.000	0.000	0.000	1	19	5
13	525	2	18
14	588	1	15
15	633		106
16	700	3	107
17	751	3	101
18	796		107
19	858	2	102
20	917		1111
Sheet1

Cell Formulas
Range	Formula
F10	=IFERROR(INDEX(C2:INDEX(C:C, $K10), $L10), "")
F11	=IFERROR(INDEX(INDEX(C:C, $K10 + 1):INDEX(C:C,$K11), $L11), "")
G10	=IFERROR(SUMPRODUCT(($A$2:$A$400000<=$E10)*($B$2:$B$400000=$F$7)*($C$2:$C$400000)) / $J10, "")
G11	=IFERROR(SUMPRODUCT(($A$2:$A$400000>$E10)*($A$2:$A$400000<=$E11)*($B$2:$B$400000=$F$7)*$C$2:$C$400000) / $J11, "")
H10	=IFERROR(SUMPRODUCT(($C$2:$C$400000 - G10)^2 * ($A$2:$A$400000<=$E10) * ($B$2:$B$400000=$F$7)) / $J10, "")
H11	=IFERROR(SUMPRODUCT(($C$2:$C$400000 - $G11)^2 * ($A$2:$A$400000>$E10) * ($A$2:$A$400000<=$E11) * ($B$2:$B$400000=$F$7)) / $J11, "")
I10	=IFERROR(SQRT($H10), "")
I11	=IFERROR(SQRT($H11), "")
J10	=COUNTIFS($A$2:$A$400000, "<="&E10, $B$2:$B$400000,$F$7)
J11	= COUNTIFS($A$2:$A$400000, ">" & $E10, $A$2:$A$400000, "<="&$E11, $B$2:$B$400000,$F$7)
K10	=MATCH($E10, A:A)
K11	=MATCH($E11, A:A)
L10	=IFERROR(MATCH($F$7, B2:INDEX(B:B, $K10), 0), "")
L11	=IFERROR(MATCH($F$7, INDEX(B:B, $K10 + 1):INDEX(B:B, $K11), 0), "")

 

[thisoldman]

See my next message

 

[thisoldman]

I don't know what I was thinking yesterday. Here are some better formulas for the calculations by stream:

Book1
	E	F	G	H	I	J	K	L
7	TCP to Match	1
8
9	Interval_end	First Rdng	Mean	Variance	Std. Dev.	Count	match.intvl	match.TCP
10	300	7	6.000	3.000	1.732	4	9	3
11	600	11	13.000	2.667	1.633	3	14	1
12	900					0	19
Sheet1

Cell Formulas
Range	Formula
F10	=IFERROR(INDEX(C2:INDEX(C:C, $K10), $L10), "")
F11	=IFERROR(INDEX(INDEX(C:C, $K10 + 1):INDEX(C:C,$K11), $L11), "")
G10	=IFERROR(AVERAGEIF(B2:INDEX(B:B, $K10), $F$7, C2:INDEX(C:C, $K10)), "")
G11	=IFERROR(AVERAGEIF(INDEX(B:B, $K10+1):INDEX(B:B, $K11), $F$7, INDEX(C:C, $K10+1):INDEX(C:C, $K11)), "")
H10	=IFERROR(SUMPRODUCT((C2:INDEX(C:C, $K10) - $G10)^2 * (B2:INDEX(B:B, $K10) = $F$7)) / $J10, "")
H11	=IFERROR(SUMPRODUCT((INDEX(C:C, $K10+1):INDEX(C:C, $K11) - $G11)^2 * (INDEX(B:B, $K10+1):INDEX(B:B, $K11)=$F$7)) / $J11, "")
I10	=IFERROR(SQRT($H10), "")
I11	=IFERROR(SQRT($H11), "")
J10	=COUNTIF(B2:INDEX(B:B,$K10), $F$7)
J11	=COUNTIF(INDEX(B:B, $K10+1):INDEX(B:B, $K11), $F$7)
K10	=MATCH($E10, A:A)
K11	=MATCH($E11, A:A)
L10	=IFERROR(MATCH($F$7, B2:INDEX(B:B, $K10), 0), "")
L11	=IFERROR(MATCH($F$7, INDEX(B:B, $K10 + 1):INDEX(B:B, $K11), 0), "")

 

[yyl05]

That solved it, thank you so much for your help!!

 

[thisoldman]

You're welcome. It's good to know it worked for you.