Intermediate Queries


0) Introduction:
1) General Filtering Technique:
2) Referring to Cells (in which we enter values to change the query quickly):
3) Counting Records with Excel (array) Formulas
4) Summary:


0) Introduction:

In the previous lecture, we learned about autofiltering to display database
records that satisfied (or conformed to) certain criteria. This was a simple
way to query/filter the records in the database, but it was a bit limited.

Using autofilters, the criteria for display allowed limited relationships
between the values in columns and specific values (numbers), but not general
Excel formulas. For example, there was no way to write a query that displayed
students whose percentage was greater than the calculated course median (unless
we calculated the course median in another cell and then copied that value into
one of the autofilter fields). That is, there was no way for the autofilter to
refer to this calculated value directly.

Also, the individual criteria for columns were always implicitly ANDed
together. So, we could not specify a criteria to display all books that 
was copyrighted before the year 2000 OR cost < 10.00. Likewise, for any
specific column, we could specify at most two relationships (which could either
be ANDed or ORed together).

In this lecture we will learn a method to perform truly general filtering,
making full use of Excel's capacity for calculating formulas. In the final
lecture in this series, we will see an easier form to enter the formulas for
queries, and also discuss a few database functions that use this form to count
or do other calculations with the selected records.


1) General Filtering Technique:

The main idea to implement this kind of filtering is simple to conceptualize.
First, we add an extra, special, column in front of all the other columns in
the database. Then, we put any boolean formula in this column, relying on
values in that row and other calcluations, which will calculate whether or not
to display the record on that line: the value will be TRUE for a record that we
want to display and FALSE for a record that we don't want to display. Finally,
we will trivially autofilter this one column, choosing to display all records
that have this boolean value matching TRUE.

First, let's apply this method to construct a formula that is equivalent to one
that we could also specify easily with autofiltering: one where we want to
display all books in the Theory "Category", whose "Cost" is > 50, and whose
"Year" is  >= 2000.

After adding a new column at front, we would specify the formula in the cell A2
to test this critera on row 2 as follows (recall that A1, B1, C1, etc. still
store the field/column names)

  =AND(G2="Theory", F2>50, E2>=2000)

This formula says, we want to display records that satisfy all three criteria:
(1) the "Category" column (G: it used to be column F, but we added a new
column at the beginning for the boolean values) specifies Theory; (2) the
"Cost" column (F: it used to be column E) specifies a value >50; (3) the
"Year" column (E: it used to be column D) specifies a value >= 2008.

Recall that when we write text in formulas, we enclose the text in quotes
(e.g., "Theory"). We use relative addressing for G2, F2, and E2 so that when
we copy or fill the cell into other rows, each of the formulas on those rows
refers to the record's values on those rows.

Here is a new way to use the Excel fill command. If we select cell A2 after
entering its formula, and double-click the fill handle (the small square in the
lower-right corner of the cell), Excel will fill the column downward with
copies of our formulas. How far down? It goes as far down as the column on
its left; if there is no column on its left, it goes as far down as the column
on its right.

Finally, if we autofilter the first column, and select to display only those
formulas that calculate TRUE, we will see all the specified books (17 of
them).

-->Open the the workbook distributed with this lecture.
-->Follow the directions above and verify that 17 records are displayed

Now let's extend the formula to include these books, as well as any books
written by Knuth. We will need to use the OR function in our formula. First we
use the autofilter to show all values in column A (not just the TRUE ones
selected for the previous formula), and then we edit the formula in cell A2 to
become

  =OR( B2="Knuth", AND(G2="Theory", F2>50,E2>=2000) )

We again double-click the fill-handle to fill this new formula down to the
last record, and finally use the autofilter to display only those formulas
that calculate TRUE.

In this formula, we specify either books written by Knuth OR Theory books
that cost more than $50 and were published in the year 2000 or later. The
result should be 22 books: 5 more written by Knuth, none of which satisfied
the other criteria.

-->Follow the directions above and verify that 22 records are displayed

If we wanted, we could further restrict the Knuth books to cost less than $30,
writing the formulas as

  =OR(  AND(B2="Knuth",F2<30),  AND(G2="Theory", F2>50,E2>=2000)  )

With this specification, only 2 of the 5 Knuth books are displayed, as the
other 3 cost more than $30.

-->Follow the directions above and verify that 19 records are displayed

We can use this general form for querying: we OR together formulas that
AND together specifications on the columns for one row. So, each AND function
determines whether or not to display a record, and the overall OR function
will display any record that satisifes at least one AND specification. Of
course to satisfy an AND specifications all parts of it must be TRUE.

Now that we are using general Excel formulas, we can use any of its powerful
functions in our column A formulas.

For example, suppose that we want to list Knuth books published after 1995 that
cost less than the average cost of all books, and Theory books published in the
year 2000 or later that cost less than the average cost of all books. We could
write the following formula in cell A2 and fill it down column A. Here it is
writen on multiple lines to simplify reading it and emphase its OR of AND
structure.

=OR(  AND(B2="Knuth",  F2<AVERAGE($F:$F), E2> 1995),
      AND(G2="Theory", F2<AVERAGE($F:$F), E2>=2000)
   )

The result of this formula displays 12 records (one by Knuth, the rest Theory
books).

-->Follow the directions above and verify that 12 records are displayed

We could rewrite this query an equivalent one by "factoring out" the common
(and complicated) average computation. 

  =AND(F2<AVERAGE($F:$F),
       OR(AND(B2="Knuth",E2>1995),
          AND(G2="Theory",E2>=2000)
         )
      )

-->Follow the directions above and verify that 12 records are displayed

We can read it as selecting records whose cost is less than the average,
AND whose name is Knuth and is published after 1995 OR whose category is
Theory and is published in the year 2000 or later.

While this formula is smaller than the one above, it departs from the standard
OR of AND structure, and is probably more complicated to understand because it
uses more logical functions, nested inside each other: 3 AND and 1 OR (a total
of 4) instead of 2 AND and 1 OR (a total of 3) nested in the standard way.
Generally we will stick to the OR of AND form, which will also be used in the
next lecture when  we specify these formulas more simply.

As a final example, if we wanted to show the 10 most expensive books, we could
use the following formula in column A, which is true if the rank of the cost
for the book is 1-10 when looking at the cost of all books.

  =RANK(F2, $F:$F) <= 10

In a similar way, we could easily select the 10 books with the earliest
publication date.


2) Referring to Cells (in which we enter values to change the query quickly):

Because we can use general Excel functions in our formulas, we can write a 
formula that refers to the values in other cells. For example, suppose that we
wanted to use cell A1 to specify a category, cell B1 to specify a minimum
price, and cell C1 to specify a year. We could insert a blank row 1 and
put the following formula in cell A3 (the column descriptions are now on row
2) to A1276:

  =AND( G3=$A$1, F3>$B$1, E3>=$C$1 )

This formula specifies a category specified by cell A1, a price greater than
the one specified in cell B1, and a publication year greater than or equal to
the one specified in cell C1. We must specify absolute addresses for the values
on row 1, so when we fill down these formulas these references down't change.

If we put/fill this formula in cells A3:A1276, and enter Theory, 50, and 2000
in the cells A1, B1, and C1, and autofilter the TRUE values, we will see 17
records that satisfy this criteria.

-->Follow the directions above and verify that 17 records are displayed

If we wanted to look a books in the AI category, costing over $30, and 
published in the year 2005 or later, we would only have to enter AI, 30, and
2005 into cells A1, B1, and C1, and then autofilter the TRUE values again
(there should be 5 displayed). Now, we don't have to change the formulas for
this new query, because they stay the same: only the values in the top-row
cells that the formulas use.

-->Follow the directions above and verify that 5 records are displayed

Of course, we could also enter a formula like AVERAGE($F:$F) in cell B1. If
we do so and then autofilter the TRUE values, there should be 6 records
displayed (the average price is 26.33, which is a bit less than the 30
previously entered in this cell, so only 1 more book satisfies the new
criteria).

-->Follow the directions above and verify that 6 records are displayed

In addition, we could create a scrollbar (for example, whose min is 1990 and
whose max is 2010) and link it to cell C1. Now we can use the scrollbar to
set the publication year, and then autofilter the TRUE values for the newly
specified year.

Finally, when using cells at the top of a column to store values that the
boolean formula uses, it is better to not autofilter the entire column, but
instead to autofilter everything below these formulas. 

If we select a cell (say A2, not the entire A column) and click the Autofilter
icon, the disclosure triangle appears in cell A2. Now, when to autofilter the
cells in row 1 (where we are putting the information used in the boolean
formulas) remains on the worksheet even after we have filtered the values in
column A (A2 and beyond). So we can easily see/remember/change the query
values.


3) Counting Records with Excel (array) Formulas

We can easily perform a counting operation on the logical formulas in column A.
Going back to entering Theory, 50, and 2000 in A1, B1, and C1, we enter the
formula 

  =COUNTIF($A:$A,TRUE)

into cell D1 to count the number of TRUE values (which is 17), even without
using autofilter to display the records. Here we can change the values in A1,
B1, and C1 and immediately see the count change in D1, without reactivating
the autofiltering.

-->Follow the directions above (and autofilter) and verify that the counts are
-->  correct.

In fact, it might be useful to define a name for the A column, say Selected. If
we defined Selected as $A:$A, then we could write the formula more simply, as

  =COUNTIF(Selected,TRUE)

An even better idea would be to define similiar names for EVERY column, so that
we can easily use these names in the array expressions that we will examine
below. We can define all these names quickly as follows

-->Select A2:I1276 (the entire database, including the fields/column names,
-->  but not the top row
-->On the Formulas tab in the Defined Names group, select Create from Selection
-->Ensure the check is in the Top Row, which means use the names in the top row
-->  to be defined as all the data in that column
-->Click OK

Now, examine the Name Manager (also on the Formulas tab in the Defined Names
group) and note that Excel has defined 10 names: the field/columns names -
assuming Selected is the field in column A; note Authors(s) is translated to
Author_s. Each name is defined to be the range of cells undereath it: e.g.,
Author_s is defined to be =Books!$B$3:$B$1276. Also note that each name is
scoped to the entire Workbook (automatically, by Create from Selection).

Given these names, we can use array formulas to do other kinds counting easily.
First, let's look at counting information over the entire database, then we
will look at computing information only from selected records (not the entire
database).

Suppose that we want to know how many books have a price greater than the
average price. We could put the formula =F3>AVERAGE(Cost) in cell A3, copy/fill
it throughout all the cells in column A, and then count the number of selected
records (TRUEs) using the formula above,  =COUNTIF(Selected,TRUE)
 
We can instead just use the array formula (in any cell, or as a defined name)

  {=SUM( 1 * (Cost>AVERAGE(Cost)) )}

Note that we cannot use COUNTIF with arrays, only ranges. So instead we use a
trick that we learned before: multiplying a boolean array by 1 to convert it
into an array of 1s and 0s (1s where there were TRUEs; 0s where there were
FALSEs). So summing the array calculates a result that is the same as the
number of 1s (or number of TRUEs) in the array.

Let's look a bit more carefully at how Excel calculates this formula

  Cost>AVERAGE(Cost) calculates an array of BOOLEAN values (the same size as
                     the Cost range), indicating whether each value in the Cost
                     range is > the average of all the values in the Cost range

  1 * above result   calculates an array of INTEGER values (the same size as
                     the BOOLEAN array result above), with 1s where there
                     are TRUEs and 0s where there are FALSEs

  SUM(above result)  calculates the sum of all the 1s, which is the same as the
                     the number of TRUEs in the original BOOLEAN array

As a final example, lets compute the average cost of all the records SELECTED
by the formula in column A (not of all the records in the database). We can
again use a short array formula

  {=AVERAGE( IF(Selected,Cost,"") )}

Here IF(Selected,Cost,"") calculates an array (the same size as the Selected
and Cost ranges). If the first Selected BOOLEAN value is TRUE, the first value
in the resulting array is the first value in Cost; but if the first Selected
BOOLEAN value is FALSE, the first value in the resulting array is "".

Likewise, if the second Selected BOOLEAN value is TRUE, the second value in the
resulting array is the second value in Cost; but if the second Selected BOOLEAN
value is FALSE, the second value in the resulting array is "".

So, the resulting array contains the costs of all the books that are selected
interspersed with "" for all the books that aren't selected. The AVERAGE
function computes the sum of the numeric values divided by a count of the
number of numeric values, which is the average cost for all the selected
books. So, it ignores all the "" values.

We could substitute other statistical functions like MEDIAN, MIN, and MAX for
AVERAGE. We could also use LARGE in the array formula, such as

  {=LARGE( IF(Selected,Cost,"") , {1;2;3} )}

to compute an array with the three largest costs of the selected books. To
display these we select three columns and put this array formula in the top
cell and press control/shift/enter. Likewise we could use SMALL to compute an
array of the smallest prices. 

Of course, there are many possible queries we might want to perform on a
database, relating to displaying records or computing statistics on the whole
database or just selected records. Each might require some ingenuity in
determining an appropriate Excel formula. We have examined a few simple but
powerful formulas for computing statistics.


4) Summary:

Here is a quick summary of skills to acquire from working on this lecture.
You should be able to discuss each of these topics a bit, but more importantly
know HOW TO DO something in Excel,

Know how to augment a database with a column in which BOOLEAN formulas
determine which records to select (and how to autofilter the TRUE records).

Know how to count the number of records whose BOOLEAN formula is TRUE

Be more familiar with using BOOLEAN array formulas to calculate a count
or the number of records that have some property in the entire database,
or some formula applied to just the selected records.