I have a worksheet called "net" containing the following:

NETWORK AVG MIN MAX STD SAMPLES
ABC 17.17 16.26 17.71 0.4469 19
CBS 12.99 11.69 14.56 0.6524 30
NBC 15.39 14.08 16.70 0.7323 38
NBC* 15.12 14.08 15.99 0.4910 31
Fox 10.63 9.07 12.09 0.9374 8
HBO 10.94 9.07 13.22 1.2507 19
Showtime 11.52 6.84 13.23 1.9017 11
HDNet 18.14 14.22 18.85 1.1619 14
HDNet* 18.45 18.13 18.85 0.2847 13
HDNet-Movies 18.08 16.54 18.49 0.7583 6
HDNet-Movies* 18.39 18.33 18.49 0.0642 5
Discovery 17.49 15.27 18.39 1.4963 4
ESPN 18.59 17.98 18.93 0.4508 4
TNT 18.22 17.97 18.37 0.1659 5

In the same file, I have a sheet called "series" which looks like this:

NETWORK SERIES AVG MIN MAX STD SAMPLES DEL
STD
ABC Boston Legal 17.03 16.26 17.71 0.5963 5
-0.3201
ABC Lost 17.15 16.90 17.44 0.2265 4
-0.0527
ABC NYPD Blue 17.03 16.70 17.97 0.4982 6
-0.3119
CBS CSI: CSI 12.78 12.41 13.12 0.2679 7
12.7843
CBS CSI: Miami 12.96 12.17 13.36 0.4429 6
12.9633
CBS CSI: NY 13.04 12.41 13.36 0.5485 3
13.0433
CBS Cold Case 13.78 13.06 14.56 0.5400 5
13.7800
CBS Without A Trace 12.59 11.69 13.84 0.7302 8
12.5875
HBO CarnivÃ*le 10.49 10.31 10.74 0.2250 3
10.4867
HDNet Smallville 18.40 18.13 18.85 0.2800 9
18.3967
NBC Crossing Jordan 15.43 15.03 15.99 0.4996 3
15.4300
NBC ER 15.55 15.27 15.99 0.3533 6
15.5500
NBC Las Vegas 16.65 16.46 16.70 0.1073 5
16.6520
NBC Law & Order 15.50 14.08 18.29 1.3278 7
15.4971
NBC Law & Order: SVU 14.91 14.32 15.51 0.4378 7
14.9129
NBC Law & Order: CI 16.44 16.23 16.64 0.2899 2
16.4350
NBC Medical Investigation 15.03 14.79 15.27 0.3394 2
15.0300

Both of these tables are in the upper lefthand corner of the sheet, labels
starting at $A$1, first row of data starting with $A$2.

In the "DEL STD" column of the "series" sheet is the following formula:


=((C2-IF(net!$A$2:$A$15=A2,net!$B$2:$B$15))/IF(net!$A$2:$A$15=A2,net!$E$2:$E$15,1))

This is trying to extract the values from the "net" sheet in columns B and E
on the row where the value in column A is the same as it is on the current
row of "series". The term:

IF(net!$A$2:$A$15=A2,net!$B$2:$B$15)

works perfectly for the first 3 rows of data in "series" (2:4), but not for
any of the others. I tried copying the cell containing "CBS" on "net" and
pasting it into the cells in column A with no results. (I assume that
whatever's wrong with the first call to IF is wrong with the second).

What am I doing wrong???

Try the following...

H2, copied down:

=(C2-VLOOKUP(A2,net!$A$2:$F$15,2,0))/IF(ISNA(VLOOKUP(A2,net!$A$2:$F$15,5,
0)),1,VLOOKUP(A2,net!$A$2:$F$15,5,0))

Since you have 2 sets of statistics for some of the networks in Column A
of your "net" table, make sure that Column A in your "series" table
specifies the correct one, by including or not including the asterisk.

Hope this helps!

In article ,
"mikeyts" wrote:

I have a worksheet called "net" containing the following:

NETWORK AVG MIN MAX STD SAMPLES
ABC 17.17 16.26 17.71 0.4469 19
CBS 12.99 11.69 14.56 0.6524 30
NBC 15.39 14.08 16.70 0.7323 38
NBC* 15.12 14.08 15.99 0.4910 31
Fox 10.63 9.07 12.09 0.9374 8
HBO 10.94 9.07 13.22 1.2507 19
Showtime 11.52 6.84 13.23 1.9017 11
HDNet 18.14 14.22 18.85 1.1619 14
HDNet* 18.45 18.13 18.85 0.2847 13
HDNet-Movies 18.08 16.54 18.49 0.7583 6
HDNet-Movies* 18.39 18.33 18.49 0.0642 5
Discovery 17.49 15.27 18.39 1.4963 4
ESPN 18.59 17.98 18.93 0.4508 4
TNT 18.22 17.97 18.37 0.1659 5

In the same file, I have a sheet called "series" which looks like this:

NETWORK SERIES AVG MIN MAX STD SAMPLES DEL
STD
ABC Boston Legal 17.03 16.26 17.71 0.5963 5
-0.3201
ABC Lost 17.15 16.90 17.44 0.2265 4
-0.0527
ABC NYPD Blue 17.03 16.70 17.97 0.4982 6
-0.3119
CBS CSI: CSI 12.78 12.41 13.12 0.2679 7
12.7843
CBS CSI: Miami 12.96 12.17 13.36 0.4429 6
12.9633
CBS CSI: NY 13.04 12.41 13.36 0.5485 3
13.0433
CBS Cold Case 13.78 13.06 14.56 0.5400 5
13.7800
CBS Without A Trace 12.59 11.69 13.84 0.7302 8
12.5875
HBO CarnivÃ*le 10.49 10.31 10.74 0.2250 3
10.4867
HDNet Smallville 18.40 18.13 18.85 0.2800 9
18.3967
NBC Crossing Jordan 15.43 15.03 15.99 0.4996 3
15.4300
NBC ER 15.55 15.27 15.99 0.3533 6
15.5500
NBC Las Vegas 16.65 16.46 16.70 0.1073 5
16.6520
NBC Law & Order 15.50 14.08 18.29 1.3278 7
15.4971
NBC Law & Order: SVU 14.91 14.32 15.51 0.4378 7
14.9129
NBC Law & Order: CI 16.44 16.23 16.64 0.2899 2
16.4350
NBC Medical Investigation 15.03 14.79 15.27 0.3394 2
15.0300

Both of these tables are in the upper lefthand corner of the sheet, labels
starting at $A$1, first row of data starting with $A$2.

In the "DEL STD" column of the "series" sheet is the following formula:


=((C2-IF(net!$A$2:$A$15=A2,net!$B$2:$B$15))/IF(net!$A$2:$A$15=A2,net!$E$2:$E$1
5,1))

This is trying to extract the values from the "net" sheet in columns B and E
on the row where the value in column A is the same as it is on the current
row of "series". The term:

IF(net!$A$2:$A$15=A2,net!$B$2:$B$15)

works perfectly for the first 3 rows of data in "series" (2:4), but not for
any of the others. I tried copying the cell containing "CBS" on "net" and
pasting it into the cells in column A with no results. (I assume that
whatever's wrong with the first call to IF is wrong with the second).

What am I doing wrong???

Thanks! Of course, I have no idea _how_, but but your formula works. I've
been a professional programmer for 30 years, and I still find Excel and
things like it to be maddeningly strange (mostly because I use them so
infrequently). 8^)

The networks with two sets of stats had one or more anomalous samples; the
second set are statistics as calculated with those points excluded. Since I
certainly want to use the sigma from the "corrected" stats the the column H
number on the "series" sheet, I had to change the network name in the first
column--thanks. It turned out that I had to use something other than
asterisk, since "NBC*" matches "NBC" (I changed to "+").

In case you were curious, this is data I've collected for average cable HDTV
bitrates on my San Diego Time Warner system, as derived from filesizes of DVR
recordings. There is a third sheet called "rbs" (for "results by series")
containing samples for individual recordings, giving filesize, duration,
network, date, time and program name from which the "net" and "series" sheets
are calculated.

"Domenic" wrote:

Try the following...

H2, copied down:

=(C2-VLOOKUP(A2,net!$A$2:$F$15,2,0))/IF(ISNA(VLOOKUP(A2,net!$A$2:$F$15,5,
0)),1,VLOOKUP(A2,net!$A$2:$F$15,5,0))

Since you have 2 sets of statistics for some of the networks in Column A
of your "net" table, make sure that Column A in your "series" table
specifies the correct one, by including or not including the asterisk.

Hope this helps!

In article ,
"mikeyts" wrote:

I have a worksheet called "net" containing the following:

NETWORK AVG MIN MAX STD SAMPLES
ABC 17.17 16.26 17.71 0.4469 19
CBS 12.99 11.69 14.56 0.6524 30
NBC 15.39 14.08 16.70 0.7323 38
NBC* 15.12 14.08 15.99 0.4910 31
Fox 10.63 9.07 12.09 0.9374 8
HBO 10.94 9.07 13.22 1.2507 19
Showtime 11.52 6.84 13.23 1.9017 11
HDNet 18.14 14.22 18.85 1.1619 14
HDNet* 18.45 18.13 18.85 0.2847 13
HDNet-Movies 18.08 16.54 18.49 0.7583 6
HDNet-Movies* 18.39 18.33 18.49 0.0642 5
Discovery 17.49 15.27 18.39 1.4963 4
ESPN 18.59 17.98 18.93 0.4508 4
TNT 18.22 17.97 18.37 0.1659 5

In the same file, I have a sheet called "series" which looks like this:

NETWORK SERIES AVG MIN MAX STD SAMPLES DEL
STD
ABC Boston Legal 17.03 16.26 17.71 0.5963 5
-0.3201
ABC Lost 17.15 16.90 17.44 0.2265 4
-0.0527
ABC NYPD Blue 17.03 16.70 17.97 0.4982 6
-0.3119
CBS CSI: CSI 12.78 12.41 13.12 0.2679 7
12.7843
CBS CSI: Miami 12.96 12.17 13.36 0.4429 6
12.9633
CBS CSI: NY 13.04 12.41 13.36 0.5485 3
13.0433
CBS Cold Case 13.78 13.06 14.56 0.5400 5
13.7800
CBS Without A Trace 12.59 11.69 13.84 0.7302 8
12.5875
HBO Carnivà le 10.49 10.31 10.74 0.2250 3
10.4867
HDNet Smallville 18.40 18.13 18.85 0.2800 9
18.3967
NBC Crossing Jordan 15.43 15.03 15.99 0.4996 3
15.4300
NBC ER 15.55 15.27 15.99 0.3533 6
15.5500
NBC Las Vegas 16.65 16.46 16.70 0.1073 5
16.6520
NBC Law & Order 15.50 14.08 18.29 1.3278 7
15.4971
NBC Law & Order: SVU 14.91 14.32 15.51 0.4378 7
14.9129
NBC Law & Order: CI 16.44 16.23 16.64 0.2899 2
16.4350
NBC Medical Investigation 15.03 14.79 15.27 0.3394 2
15.0300

Both of these tables are in the upper lefthand corner of the sheet, labels
starting at $A$1, first row of data starting with $A$2.

In the "DEL STD" column of the "series" sheet is the following formula:


=((C2-IF(net!$A$2:$A$15=A2,net!$B$2:$B$15))/IF(net!$A$2:$A$15=A2,net!$E$2:$E$1
5,1))

This is trying to extract the values from the "net" sheet in columns B and E
on the row where the value in column A is the same as it is on the current
row of "series". The term:

IF(net!$A$2:$A$15=A2,net!$B$2:$B$15)

works perfectly for the first 3 rows of data in "series" (2:4), but not for
any of the others. I tried copying the cell containing "CBS" on "net" and
pasting it into the cells in column A with no results. (I assume that
whatever's wrong with the first call to IF is wrong with the second).

What am I doing wrong???