Spreadsheet Tricks for Thermal Engineers : Data Analysis, continued
To use database functions, first set up the data in the correct format. Then use two new blank lines. The first will contain the filtering headings -- these are the same headings as you use for
the data. You can either enter them by hand (tedious), copy the database headings line (less tedious), or reference the database heading cell in the same column ("=B7", for example). This third
method is the handiest, because it will keep up with any changes you later might make to the headings -- spelling changes, for instance. The second new blank line, directly below the first, will
contain the filtering criteria for the heading above it. You can use standard logicals here; one example might be ">9.5" entered below "fan voltage." Finally, in any convenient column (I use
the same column as the result of interest), enter the database function; for example, "=DAVERAGE(database_range,field,criteria_range) will look at the data in database_range (using range names is
handy; otherwise, use the F4 key to toggle until you get all dollar signs everywhere). It will take the average of all the "field" data (specify "field" either as a column number, or retype the
heading in double quotes) that matches the criteria you've specified in a two-row criteria_range. The view below shows how this looks when the "Formulas" box is checked under Tools/Options/View.
=A6 |
=B6 |
<3 |
=DAVERAGE($A$6:$B$8,2,$A$1:$A$2) |
| |
|
| |
|
| |
|
label1 |
label2 |
2 |
5 |
3 |
7 |
and here it is with the normal view:
label1 |
label2 |
<3 |
5 |
| |
|
| |
|
| |
|
label1 |
label2 |
2 |
5 |
3 |
7 |
There are several other database functions that work similarly. These are handy for looking at data from a
designed experiment -- it's easy to evaluate data for different levels of factors or combinations of factors. In fact, the hardest part is getting the averages out into a
form that allows easy presentation; I haven't found any easy way to do this without tediously writing a huge spreadsheet, because the database function really
wants the column headings to be in a row immediately above the criteria. It seems crazy, but the least effort method seems to be writing the resulting averages
down -- on paper, with a handheld writing implement (pen!), in a lab notebook -- and then retyping them. I suppose you could do the same thing by copying the
row contents, and pasting-as-values in a summary sheet. If you haven't used paste as value, it's Edit/Paste Special with the Values box checked. I use
the keystroke sequence Alt-E-S-V to do this. You can also customize your toolbar to include a button; it looks like a clipboard with the digits 12 on it, and lives in the Edit toolbar. |
