Column searching problem
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??? |
All times are GMT +1. The time now is 01:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com