Spreadsheet: 139399d1328217226-preparing-an-excel-spreadsheet-for-linking-need-attachments-for-clarification-report
Sheet: Sheet1

DATE OF REPORT: |  | EFF | EFF | INF | EFF | EFF | EFF | EFF | EFF | EFF | EFF | INF
 |  | FLOW | PEAK | FLOW | Cl2 USED | NaHSO3 | CL2 RES  mg/L | CL2 RES  mg/L | SET SOL | DO | TURBIDITY | SUSP SOLIDS
Date |  | MGD | MGD | MGD | [NAME]/Day | GAL/DAY | Total/Free | Total/Free | ML/L | MG/L | NTU | mg/L
2011-10-01 00:00:00 |  | METERED | METERED | METERED | RECORDED | RECORDED | AM GRAB | PM GRAB | GRAB | GRAB | GRAB | 24-HR COMP
 |  | DAILY | DAILY | DAILY | DAILY | DAILY | DAILY | DAILY | 5/WEEK | MONTHLY | Every 10 Days | MONTHLY
 | Column offset | 14 | 15 | 16 | 17
 | DAY | Datapoint 1 | Datapoint 2 | Datapoint 3 | Datapoint 4
REMARKS: | 1 |  |  |  |  | 413 | <0.1 | <0.1 | <0.1 | 9 | 1.42 | 400
 | 2 |  |  |  |  | 476 | <0.1 | <0.1 | <0.1 | 8.3 | 2.57 | 340
NAME OF | 3 |  |  |  |  | 570 | <0.1 | <0.1 | <0.1 | 8.5 | 1.99 | 197
CONTACT | 4 |  |  |  |  | 408 | <0.1 | <0.1 | <0.1 | 8.3 | 1.78 | 213
PERSON: | 5 |  |  |  |  | 458 | <0.1 | <0.1 | <0.1 | 9.1 | 2.63 | 312
 | 6 |  |  |  |  | 390 | <0.1 | <0.1 | <0.1 | 8.6 | 8.47 | 267
 | 7 |  |  |  |  | 401 | <0.1 | <0.1 | <0.1 | 9 | 3.41 | 342
 | 8 |  |  |  |  | 374 | <0.1 | <0.1 | <0.1 | 8.6 | 2.05 | 389
 | 9 |  |  |  |  | 387 | <0.1 | <0.1 | <0.1 | 8.6 | 2.92 | 471
 | 10 |  |  |  |  | 361 | <0.1 | <0.1 | <0.1 | 9.1 | 4.08 | 453
 | 11 |  |  |  |  | 251 | <0.1 | <0.1 | <0.1 | 8.8 | 2.93 | 532 |  | What | Functions Used | Result | [NAME]
 | 12 | 5.44 | 1.102941176470588 | 24.73345588235294 | 15.9880128521997 | 419 | <0.1 | <0.1 | <0.1 | 8.6 | 3.22 | 452 |  | [NAME] minimal input to date for lookup | DATE,YEAR,MONTH | 2011-10-12 00:00:00
 | 13 |  |  |  |  | 364 | <0.1 | <0.1 | <0.1 | 8.2 | 2.65 | 416 |  | Find where the date is in the array RawDataArrayin the other workbook - 17th row down in the array | MATCH | 17
 | 14 |  |  |  |  | 346 | <0.1 | <0.1 | <0.1 | 9.1 | 3.29 | 406 |  | INDEX(reference,row number, column number) this takes the row that | INDEX | 5.44
 | 15 |  |  |  |  | 347 | <0.1 | <0.1 | <0.1 | 9.2 | 2.79 | 282 |  | the date was entered into - in this case row 17 of the array, not the
 | 16 |  |  |  |  | 368 | <0.1 | <0.1 | <0.1 | 9.3 | 3.07 | 379 |  | worksheet row.  I added 4 to it since the data you want is 4 rows down
 | 17 |  |  |  |  | 339 | <0.1 | <0.1 | <0.1 | 8.8 | 4.89 | 374 |  | from the date that was looked up - so row 21.  Then the data you want is
 | 18 |  |  |  |  | 438 | <0.1 | <0.1 | <0.1 | 8.3 | 3.77 | 371 |  | the 14th column from the left so I added the column numbers at the top of
 | 19 |  |  |  |  | 432 | <0.1 | <0.1 | <0.1 | 8.6 | 4.39 | 416 |  | this report to correspond to what you want. What you get then is the
 | 20 |  |  |  |  | 383 | <0.1 | <0.1 | <0.1 | 8.6 | 2.48 | 471 |  | intersection of the row and the columns - colored in the other worksheet
 | 21 |  |  |  |  | 319 | <0.1 | <0.1 | <0.1 | 8.8 | 3.68 | 615 |  | There are some absolute addresses  i.e. $[NAME] which is the day of the month
 | 22 |  |  |  |  | 370 | <0.1 | <0.1 | <0.1 | 9.6 | 2.23 | 573 |  | and the absolute row of the column offset number.  That way you can
 | 23 |  |  |  |  | 409 | <0.1 | <0.1 | <0.1 | 9.3 | 1.94 | 596 |  | copy the formula down and across and everything still works.
 | 24 |  |  |  |  | 371 | <0.1 | <0.1 | <0.1 | 8.3 | 1.91 | 519
 | 25 |  |  |  |  | 376 | <0.1 | <0.1 | <0.1 | 8.8 | 5.41 | 504
 | 26 |  |  |  |  | 430 | <0.1 | <0.1 | <0.1 | 7.9 | 5.21 | 634
 | 27 |  |  |  |  | 411 | <0.1 | <0.1 | <0.1 | 9.1 | 3.45 | 650
 | 28 |  |  |  |  | 364 | <0.1 | <0.1 | <0.1 | 9.1 | 2.16 | 542
 | 29 |  |  |  |  | 374 | <0.1 | <0.1 | <0.1 | 8.9 | 2.68 | 376
 | 30 |  |  |  |  | 468 | <0.1 | <0.1 | <0.1 | 8.7 | 3.05 | 518
 | 31 |  |  |  |  | 652 | <0.1 | <0.1 | <0.1 | 8.7 | 4.4 | 401
MONTHLY AVERAGE |  | 3.669580645161291 | 5.982580645161289 | 4.081387096774194 | 981.6451612903226 | 402.2258064516129 | <0.1 | <0.1 | <0.1 | 8.76774193548387 | 3.255483870967742 | 432.6129032258065
MONTHLY HIGH |  | 4.352 | 11.22 | 4.611 | 1504 | 652 | <0.1 | <0.1 | <0.1 | 9.6 | 8.47 | 650
MONTHLY LOW |  | 3.231 | 4.84 | 3.817 | 775 | 251 | <0.1 | <0.1 | <0.1 | 7.9 | 1.42 | 197
RECORDINGS/MO |  | 31 | 31 | 31 | 31 | 31 | 31 | 31 | 31 | 31 | 31 | 31
REQUIREMENT 1 |  |  |  | M-AVE 5.2 |  |  |  |  | M-AVE .100 | MIN 4.0
TIMES EXCEEDED
REQUIREMENT 2 |  |  |  |  |  |  | Not greater than 2.0 [NAME] |  | MAX .100
TIMES EXCEEDED
REQUIREMENT 3
TIMES EXCEEDED
 |  | NOTES: | The calculated BOD removal rate is 99.4%.
 |  |  | The calculated TSS  removal rate is 98.2%.