Section 8.3. The Logic


8.3. The Logic

All of the calculations are done on the Workarea sheet shown in Figure 8-8.

Figure 8-8. Workarea


The values in column A are used in other calculations and are named. The names are in bold font and are above the value. The summary area in cells B22:E26 is built using the Report sheet. This makes the calculations simpler because the Report sheet only contains one queue at a time. Cell A2 is named LastRow and contains the array formula:

 {=MAX((Report!A1:A5000<>"")*ROW(Report!A1:A5000))}

This keeps up with the row number of the last row used on the Report sheet. It is only looking at the first 5,000 rows of the Report sheet, but that is enough for this application. We are going to build references to some of the columns on Report and this lets us set the range correctly.

The formula in A4 is:

 ="Report!b8:b" & LastRow

It returns the range of cells in column B, containing status, on the Report sheet. It starts at row 8 because that is where the data starts. Cells A6 and A8 are similar but reference different ranges on the Report sheet.

There are two calculation areas that populate the charts, and one named display area for the summary at the top of the detail report (Figure 8-6).

The calculation areas for the charts are in Figure 8-9.

Figure 8-9. Building the chart area


The names of the queues are in row 2 and the statuses are in column B. They are just typed in, but in a production application they could link to a settings sheet allowing the user to change the queue names and status numbers without modifying Workarea.

We need the counts for each queue and status. If we had only one criterion, queue or status, we would use the COUNTIF function. But here we have two logical tests to perform, so we use the array formula in cell C3:

 {=SUM((Data!$A$1:$A$5000=C$2)*(Data!$B$1:$B$5000=$B3)*1)}

The first part builds a list of zeros and ones, testing the values in column A of the Data sheet against the value in cell C2 (which is OCR). So this value will be 1 for rows containing items in the OCR queue. The second test checks column B on the Data sheet against the value in B3. It looks for items with a status of 1.

These lists are multiplied together and by 1, because we count one for each row that has the right queue and status. The whole thing is inside a sum function that adds up the ones, giving us the value we need.

The formula is built to be filled across and down to cell G5. In the lower area (B12:G15) we do the same thing for the OldData sheet with this formula:

 =SUM((OldData!$A$1:$A$5000=C$12)*(OldData!$B$1:$B$5000=$B13)*1)

The logic is the same. Only the sheet name and row numbers for the criteria values are different.

If more queues or statuses are required, these areas could become bigger. Each new queue would add a column and a new chart on the display sheet. Each new status adds a row and a new bar on the charts. The charts would have to be added and the display adjusted manually.

The area at the bottom of Workarea is shown in Figure 8-10. It uses the named values in column A to calculate summary totals for the report.

Cell C24 contains this formula:

 =COUNTIF(INDIRECT(BRange),A11)

Figure 8-10. The Summary calculation area


This formula counts the number of rows on the Report sheet that have a 1 (status 1) in column B. It uses the name value BRange in cell A3 for its range and the value in cell A11, which is =1, for its criteria.

This formula fills down to C26, but the criteria references have to be changed to A14 and A17 in cells C25 and C26.

The formula in cell D24 finds the oldest date/time for status 1 in the report. The formula is:

 =DMIN(INDIRECT(BHeadingRange),Report!C7,Criteria1)

We use the named value BHeadingRange because the DMIN function requires the heading. This range starts one row higher than BRange in order to include headings.

Report!C7 contains "Create Date/Time". The data for an application like this comes from a computer. If there is a change in the column heading our application will fail if we hardcode the column name in the formula. Putting the name here and using it later as a reference makes the application more stable.

This function uses the named range Criteria1 for its criteria and fills down, but the Criteria references need to be changed to Critieria2 and Criteria3.

The formula in E24 returns the total value of all items in this queue and status. The formula is:

 =SUMIF(INDIRECT(BRange),A11,INDIRECT(ERange))

This is similar to the formula in C24 but uses SUMIF and the ERange, since column E contains the value.

This area is named ReportTotals and is referenced by an array formula on the Report sheet, thus keeping all calculations on the Workarea sheet.



Analyzing Business Data with Excel
Analyzing Business Data with Excel
ISBN: 0596100736
EAN: 2147483647
Year: 2006
Pages: 101
Authors: Gerald Knight

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net