notes_to_advanced_excel by stariya

VIEWS: 7 PAGES: 23

									     Notes to the Intermediate and Advanced Excel Class for EPM Users

                                         Presented to DOT March 17 and 24, 2011


Table of Contents

Relative and Absolute references and Named Ranges ................................................................... 2
   What is a relative reference? ...................................................................................................... 2
   What is an absolute reference? .................................................................................................. 2
   What is a Named Range? ............................................................................................................ 2
   Advantages of using Named Ranges ........................................................................................... 3
   Rules for Named Ranges ............................................................................................................. 3
   Using the Name Box.................................................................................................................... 3
   To Create a Named Range .......................................................................................................... 4
Functions ......................................................................................................................................... 4
   COUNTIF ...................................................................................................................................... 4
   SUMIF .......................................................................................................................................... 5
   VLOOKUP..................................................................................................................................... 5
   SUBTOTAL ................................................................................................................................... 7
     Sorting ..................................................................................................................................... 8
     Subtotaling .............................................................................................................................. 9
Import ........................................................................................................................................... 12
Pivot Tables ................................................................................................................................... 13
   Background Information ........................................................................................................... 13
   Basic Pivot Information ............................................................................................................. 14
   Modifying Data ......................................................................................................................... 15
   Formatting ................................................................................................................................ 15
Advanced Filter to find a subset of data ....................................................................................... 17
Advanced Filter to find unique values .......................................................................................... 18
Database Functions....................................................................................................................... 19
SUMPRODUCT ............................................................................................................................... 20
Array Formulae ............................................................................................................................. 22




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                                                              Page 1 of 23
Relative and Absolute references and Named Ranges

What is a relative reference?
A relative reference occurs when a cell is used in a formula without locking its position using the
$ symbol. A relative reference means that Excel will change where the cell your formula is
pointing to if you copy or expand the cell containing the formula.

For example, =A1

When you copy this formula to another cell, Excel automatically adjusts the cell reference to
refer to different cells relative to the position of the formula.

When you enter a formula, you enter a specific cell reference (for example, in cell C1 you type
=A1*B1) Behind the scenes, Excel translates those references into positions relative to the
result cell (C1). So, the formula =A1+B1 is translated as =TwoCellsToTheLeft *
OneCellToTheLeft. You copy down the formula, Excel uses its translation to generate results.

What is an absolute reference?
An absolute (or locked) reference occurs when a cell is entered in a formula using the $ symbol.

For example:
=$A$1 - Locks the field
=A$1 - Locks the row
=$A1 - Locks the column

Each component of the cell reference can either be defined as absolute or relative. Highlighting
the cell and pressing the F4 key repeatedly will cycle you through the absolute reference
scenarios. An absolute reference means that the referenced cell will not change when you
change the position of the cell with the formula.

A named range is an example of an absolute reference. The major difference is that a named
range may referenced easily from anywhere in a spreadsheet simply by typing the range name
in a formula or by pressing F3. F3 displays the list of named ranges you have created.


What is a Named Range?
o A named range is a short text description that can be used instead of the cell address to
  refer to individual cells or ranges of cells.
o Providing cells with actual descriptive names can be used to simplify formulas and make
  them much more user friendly.
o Descriptive names are also a lot easier to remember that actual cell addresses.


66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                      Page 2 of 23
o You can also give descriptive names to constant values as well as formulas.
o Any named ranges that are created are workbook specific and can only be used in the
  workbook they are created in.
o You can also use the shortcut key (F3) to display the (Insert > Name > Paste) dialog box
  when editing a cell.
o Named ranges can be given to cells containing both numerical and text values.


Advantages of using Named Ranges
In addition to providing an alternative to repeatedly typing in cell addresses and cell ranges,
using named ranges have several other advantages.
o They improve readability and make your formulas much easier to understand meaning
    there is less chance of errors.
o Moving or shifting cells that have a named range means that the formulas are adjusted
    automatically. There is no need to worry about whether the formulas use absolute or
    relative references.
o Inserting and deleting cells, rows or columns will not change the location of your named
    ranges. Moving cells, rows or columns will though.
o Typing a descriptive name is much easier than remembering a specific cell address,
    therefore simplifying your formulas.
o Allows you to define Named Constants which are single, frequently used values.


Rules for Named Ranges
o A named range can be up to 255 characters long and can contain letters, numbers, periods
  and underscores (no spaces or special punctuation characters).
o Named ranges are not case sensitive and they can contain both upper and lower case
  letters. They cannot resemble any actual cell addresses such as "B3" or "AA12".
o All named ranges must begin with a letter, an underscore "_" or a backslash "\".
o Named ranges can include numbers but cannot include any spaces.
o You cannot use any symbols except for an underscore and a full stop”.”. It is possible to
  include a question mark as long as it is not the first character.
o Named ranges can be just single letters with the exception of the letters R and C.
o When you add a named range it is the cell that is named and not the cell contents.
o By default named ranges are created as absolute references.
o It is possible for a cell (or range) to have more than one named range so typing a new name
  using the Name Box will not change the named range but will create a new one.


Using the Name Box
o The Name Box is basically a shortcut for creating and inserting named ranges.
o A more comprehensive method is to use the (Insert > Name) submenu it is very useful for
  moving to different parts of a worksheet.


66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                       Page 3 of 23
o The drop-down list to the right of the Name Box allows you to find and select the named
  ranges.
o You can quickly insert named ranges into your formulas by pressing F3 while editing in the
  formula bar.
o If you highlight a range of cells and this corresponds exactly to a named range then the
  name is displayed in the Name Box.


To Create a Named Range
o Ctl-Shift-arrow key (down usually) to highlight the range you want to define (include the
  header)
o Ctl-Shift-F3 to open the Create Names dialog box
o Click OK
o Click the dropdown arrow from the Name Box to see the result (the Create Names dialog
  box will modify by adding underscores to any header that has two or more words in their
  title)




o

For more information, go to:
http://www.bettersolutions.com/excel/EFC133/VG113586331.htm


Functions

COUNTIF

Syntax
=COUNTIF(RangeOfThingsToCount, WhatToCount)
Example: =COUNTIF(A1:A45,”11000”)

What to count can be

¤ Cell Reference (e.g., A1)
¤ “Word(s)”
¤ formula – “>=100”


66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                  Page 4 of 23
Allows the use of Wildcards

=COUNTIF(range, “A*”) will find all things starting with A
=COUNTIF(range, “*A*”) will find all things with A in it
=COUNTIF(range, “??A*”) will all things with A in the third position followed by anything.

* = any number of spaces
? = one space

Excel 2007, 2010 has COUNTIFS for use with multiple criteria (AND criteria only)


SUMIF

Syntax
=SUMIF(RangeToBeExamined,CriteriaToMatch,RangeToTotal)
Example: =SUMIF(A1:A45,”11000”,B1:B45)

Range to be examined – a column

Criteria to match can be:

¤ Cell Reference (e.g., A1)
¤ “Word(s)”
¤ formula – “>=100”

Range to Total – a column

Note: Excel 2007 has SUMIFS for multiple criteria (AND criteria only)


VLOOKUP

The VLOOKUP function scans down the row headings at the side of a table to find a specified
item. When it is found, it then scans across the row to pick a cell entry based on its column
designation in the table. It is made up of four parts: (1) The item to find; (2) a lookup table of
two or more columns where the “item to find” makes up the left-most column and information
to be returned makes up the columns to the right; (3) a number (usually 2 or greater) that
indicates the column where the information to be returned is located; (4) TRUE or FALSE where
FALSE indicates an exact match is required and TRUE indicates that the list is sorted and the
match will be exact or the next lowest value from the “item to find.”




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                      Page 5 of 23
=VLOOKUP(ItemToFind,RangeTo LookIn,ColumnToPickFrom,SortedOrUnsorted)
Example: =VLOOKUP(A1,E19:F66,2,FALSE)

A1 = Item to Find
E19:F66 = The Range to look in to find the item This can be named range (highlight the table, in
the Name Box give it a name, press Enter. If you decide to keep just the cell reference (e.g.,
E19:F66) make sure to lock the reference using the F4 key.
2 = Once the item is found select the item in this column. This is the ‘nth’ column in the table
FALSE = An exact match is required (Assumes unsorted, but the list may be sorted)
TRUE would indicate an exact match is not required. Finds an exact value or the next lowest
value. (Assumes sorted, and the function will fail if the reference list is unsorted)




Use VLOOKUP to match one item with an associated item.




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                     Page 6 of 23
VLOOKUP Troubleshooting
Formula is Displayed Instead of Calculating a Value in Excel

When you type your formula into a cell, and you see the formula instead of a value, it means
one of two things:

You are viewing formulas. Just hit Ctrl+` (that's the accent mark near your Esc key on your
keyboard). Most people call it CTL-~ (tilde)

Your cells are formatted as text.
Highlight the affected cells
Ctl-1 to open the Format Cells dialog box
Select General from the Number tab
Click OK
In the formula bar, copy and paste your formula.
Press Enter


SUBTOTAL

Syntax
=Subtotal(method,range1,range2,…,range_n)
Method is the subtotal that you'd like to create.
It can be one of the values listed below.
Range1,range2…,range_n are the ranges of cells that you want to subtotal.

 Value   Calculation                                 Method Description
   1      AVERAGE      Adds all entries and then divides by the number of entries
   2       COUNT       Counts the number of entries containing numbers
   3      COUNTA       Counts the number of entries that are not blank (includes text entries)
   4        MAX        Reports the highest number of all the entries
   5        MIN        Reports the lowest number of all the entries
   6     PRODUCT       Multiplies all the entries together
   7       STDEV       Computes the standard deviation, assuming the selection is a sample of the
                       entire population
   8       STDEVP      Computes the standard deviation, assuming the selection is the entire
                       population
   9        SUM        Adds all entries together
   10       VAR        Computes the variance, assuming the selection is a sample of the entire
                       population
   11       VARP       Computes the variance, assuming the selection is the entire population




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                        Page 7 of 23
SUBTOTAL
SUBTOTAL allows the user to subtotal a table of data at one or more levels by sum, count, or other
defined method. It is required that the table of data being subtotaled is first sorted so that it is in the
order the user wants to subtotal.




Sorting

Sorting is accomplished first by placing the cursor anywhere inside the table of data (Excel will
determine the boundaries of the table).

Navigation: Data > Sort. The Sort dialog box opens. (In Excel 97-2003 you are allowed 3 sort
levels)




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                                Page 8 of 23
Repeat this action for all numbers that are being treated as text.

Once the data is sorted, it may be subtotaled. Place the cursor inside the table.

Subtotaling
Navigation: Data > Subtotal. The Subtotal dialog box displays.

The data to be subtotaled must match the sort criteria. In the example, the sort criteria was
Fund – SID – Acct.




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                      Page 9 of 23
One the user clicks OK, Excel creates the Subtotal and adds a summary control area to the left
of the spreadsheet. This allows the user to control which summary is displayed.




All that is required to add a different subtotal method (e.g., Count) while retaining the original
subtotal is to repeat the subtotal process, select the new method, and uncheck the “Replace
current subtotals” checkbox. Click OK.

Additionally, the same applies to adding the same subtotal method to different data. Select the
next column in the sort hierarchy; uncheck the “Replace current subtotals” checkbox. Click OK.




When an additional subtotal layer is added, the Summary Control will expand by the additional
layer. This allows the user to control the level of detail that is displayed.




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                      Page 10 of 23
In this example:
Summary 1 will show just the grand total




Summary 2 will show just the Fund subtotal. In this example, it also shows the SID subtotals to
two funds. This is because there are no changes at the SID level.




Summary 3 shows the Summary 2 subtotals and also the Summary 3 subtotals (in this example,
Fund and SID)




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                   Page 11 of 23
Summary 4 shows all of the detail along with the related subtotals.

To remove all of the subtotals, navigate to the Subtotal dialog box and click the Remove All
icon.



Import

Not all Excel spreadsheets are created by entering data. Sometimes, the data already exists and
all that is required is to import it so it can be analyzed. To make the process easier, Excel has a
‘wizard’ that walks the user through the steps. The data can be either in a CSV (comma
separated value) or TXT (text) format.

Navigation: Data > Import External Data > Import Data.

The Select Data Sources dialog box displays. Navigate to the file location of the data to be
imported; click on the file; click Open. The Text Import Wizard dialog box displays.

Step 1: Choose the file type that describes your data
Delimited: These are characters that act as field separators (This is the default setting and is
true of CSV and most TXT files).
Fixed Width: The user determines where the field separation occurs.

Click Next

Step 2: Select the correct delimiter or determine the column widths
The default is Tab, but most CSV and TXT files use a comma separator. Generally, the
instruction will be to uncheck the Tab checkbox and check the comma checkbox. Depending on
the nature of the list being imported the user can select his own defined delimiter by checking
the Other checkbox and typing a delimiter (example, @ if the list is email addresses). The only
rule is that the delimiter must apply to all the rows.




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                      Page 12 of 23
Click Next

Step 3: Column Formatting. Excel wants to interpret the data the way it looks. If something
looks like text (words, mixed words and numbers) Excel wants to treat it as text. If something
looks like a number (numbers, numbers with leading zeros) it wants to treat it as a number. If it
looks like a date then Excel wants to treat it as a date. If this is true, then the user doesn’t have
to do anything and can leave the formats as General. However, this isn’t always true.

Click the column to be formatted and select the format.

General: Excel will determine how the data should be interpreted.
Text: (Vendor, Voucher, some fields text-others numbers) Used on any column where it is
important to retain leading zeros.
Date: For fields that have a number format that should be formatted as a date. (example: the
number 40000 formatted as a date represents 7/6/2009).
Do not import column: Column will not be imported.

Click Finish. The Import Data dialog displays. This is used to determine where on the
spreadsheet the import will begin. The default is cell A1. This can be changed.

Click OK. Excel will import the data.


Pivot Tables

Background Information

Pivot tables are a way of summarizing tabular data by use of subtotals and other calculations
where the user can choose the display parameters. In this way, large tables of data can be
organized so that it can be easily reviewed and relationships identified that might otherwise be
hard to see.

Create a Pivot Table (Excel 97-2003)
Place the cursor anywhere in the table to be pivoted.

Navigation: Data > PivotTable and PivotChart Report. The PivotTable and PivotChart Wizard
displays.

Step 1: Microsoft Excel List or Database and PivotTable are selected by default. Click Next.
Step 2: So long as the cursor has been placed inside the table, Excel will determine the border
using the marching ants as an outline. If this is correct, click Next.
Step 3: New Worksheet is selected by default. There are two links on this page (Layout and
Options ~ these will be discussed later). Click Finish. The pivot table is created and the
PivotTable Field List is displayed. The pivot table is drag and drop to add or remove items.


66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                       Page 13 of 23
The following terms are used in PivotTable reports:
o Field: The header at the top of a column in a List (data source table).
o Item: Numeric data or text in the Field column.
o Data: An area detailing the data in the lower part of the PivotTable report, including
   columns with numeric data.
o Row Field: A field that is positioned as a row in the lower left of the PivotTable report.
o Column Field: A field that is positioned as a column in the row above the data in the
   PivotTable report.
o Page Field: A field that is positioned in the upper left of the PivotTable report.

Example of a Data Cube
Once constructed, the data has three dimensions
¤ X Axis
¤ Y Axis
¤ Drill Down to details


Basic Pivot Information
Pivot Wizard (part 3) and Layout button (right click > wizard > layout)
  Data: shows the results you want to see
  Column: shows results of each value in its own column
  Row: shows results of each value in its own row
  Page: allows you to filter the values of the table based on the values you select.

Options button (right click > wizard > options)
 Allows you to personalize how the data will look.

Blank table: Fields are drag and drop
  Format the Data field (comma format)
  Reorder the fields: click and drag field to the left (box, I-beam)

View the underlying data (Drill down)
  Double-click the field of interest

Filtering
   Drop down for Row and column – multiple filter options
   Page: Single Option
   Page: Pivot Table > Show Pages (creates a pivot table for each Page topic)




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                     Page 14 of 23
Modifying Data
Update the Data
  Change Source Table then go back to the pivot and click the exclamation. If you add a column,
do so inside the table. This way you don’t have to redefine the table.

Calculated Fields
  Pivot Table > Formulas > Calculated Fields
  Name: Overhead?
  Select Field from list > Insert field
  Insert calculation: *.05
  This creates an additional field that can be filtered

Grouping Data (usually used for dates)
  Right Click (header) > Group and Show Detail > Group (Group dialog opens)
  You can select single or combinations (eg, month, quarter, year)
  This creates an additional field that can be filtered

Ad hoc Grouping
   Mouse over the boundary of the multi-column or multi-row until your mouse pointer looks
like a right pointing arrow, then click. All the data that meets the criteria will be highlighted.

Add the Sum Of field twice: change one to Count of
 Right click > Field Settings > Select Count
 Right Click > Field Settings > Name (rename field)


Formatting
Pivot Chart
  Right click > Pivot Chart

Format Report
  Single Data Column (a true reformat)
  Multi Data column (Format in place – it makes the pivot data look prettier)

Show Pages
Pivot Menu > Show Pages > Select Item > Click OK

Showing percents
  Field Setting > Number > Percentage > OK > Options > Show Data As (dropdown) > % of Total
> OK




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                       Page 15 of 23
Hiding and Showing Detail
  Double click the row or column item of interest (not the header). The Show Detail dialog box
opens. Select the detail you want to show, and click OK. The item selected becomes a new
column or row. Drag back to the Pivot Table Field List once it is no longer needed.

Other Menus
  Double click the Row, Column or Data header to display the Pivot Table Field dialog box. Your
choices are
       Subtotals (Auto, Custom, None)
       Advanced (Auto Sort, Top 10)
       Layout (Display and Print options)
       Number (if a date field

Pivot Menu
  Right click the menu bar and click the PivotTable entry. By dragging the PivotTable menu to
the master menu, the pivot table menu can be anchored so it is always visible when needed.

Pivot Charts
  Not all pivoted data will lend itself to charting. By clicking the PivotTable dropdown menu and
selecting PivotChart (second line), the user can experiment with chart presentation.




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                   Page 16 of 23
Advanced Filter to find a subset of data

In practice, the Advanced Filter behaves most like a database function (see the Database
Functions section for a full explanation). It asks explicitly for a data source and a separate
Criteria area. The Field of interest is contained in the Criteria area.

Notes:
o AND/OR: Criteria on the same row constitute the AND criteria. Criteria on different rows
   constitute the OR criteria. = is assumed. Other operators include: <, <=, >=, >, <>
o If you are using the same field name twice as an AND operator then you must use <, <=, >=,
   > to make the advanced filter work properly.
o Use copy and paste for your criteria headings. You can type, but the spelling has to be exact.
o You only need to use specific criteria headings. You do not need to include the entire
   header row.
o You can specify the columns that you want to extract. If you want to extract all columns,
   you can leave the extract range empty.
o You can use the wildcards ~, *, and ?
o To find specific text use the equal sign. For example, ICE will find ice, ice cream, ice tea, etc.
   Using =”=ice” will find only those entries where the answer is ice.

Procedure
1. Highlight and copy the header row. Place the cursor where you want the criteria to appear
   and Paste. Note: The criteria only needs to include those column headings you want to use
   as criteria. If you are not using the copy and paste feature then you must type criteria
   exactly the same way as it appears in the header data field (spelling only ~ it does not need
   to be formatted the same). This becomes the criteria area.
2. Place the cursor in the data area.
3. Navigation: Data > Filter > Advanced Filter (the Advanced Filter dialog box displays)
4. Check the Copy To Another Location radio button (preferred). The default Filter the List, IN-
   Place radio button will hide the rows that don’t meet the criteria. Copy to Another Location
   allows the user to copy the subset easily to another workbook or spreadsheet.
5. List Range: Self selected so long as the cursor is placed in the data area. Otherwise, click the
   lookup and select the data area (including the headers). Click the lookup to return to the
   Advanced Filter dialog.
6. Criteria Range: Click the lookup and select the criteria area (include the header fields and
   the criteria fields (see the picture below).
7. Copy To: Click the lookup. Select where you want the unique values to display.
   o If you select just the one cell then the filter will return the same number of columns as
        are displayed in the data area.
   o To return selected columns then you must establish beforehand those column headings
        to be returned. Select the column headers you want returned and either type or copy



66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                       Page 17 of 23
       them to the Copy To location. Using the Copy To lookup, select those headers. Click the
       lookup to return to the dialog
8. Click OK.




Example as Described



Advanced Filter to find unique values

1. The Unique values feature is a subset of the filtering operation. Otherwise, it is set up in
   exactly the same way as any other filter.
2. Navigation: Data > Filter > Advanced Filter (the Advanced Filter dialog box displays)
3. Check the Copy To Another Location radio button (preferred)
4. List Range: Click the lookup. Highlight the column of data that contains your data of
   interest. You must include the column header.
5. Criteria Range: Ignore
6. Copy To: Click the lookup. Select where you want the unique values to display.
7. Check the Unique Records Only checkbox.
8. Click OK.

Note: If you are trying to find unique values from a calculated field, Excel does not like to work
with calculated functions (e.g., =SUM) because all it sees is the formula. To solve this problem,




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                     Page 18 of 23
you must copy the range and, using the Paste Special feature, paste "just values". This leaves
the data while removing the calculation.

Also, this process assumes there will be a header row, so if you don’t include the header row
Excel will show the first unique value twice.

Database Functions




=DSUM($A$10:$J$50,4,A6:J8)

=DSUM(DataSource,FieldofInterest,Criteria)

DataSource – The Data Source is the entire table of information you need to examine, including
the field names at the top of the columns.

FieldofInterest – The Field of Interest is where the answer located. It can be entered on the
formula as the column header (“Amount”), column number (4), or field reference (D10).

Criteria - The Criteria is made up of two types of information. The first set of information is the
name, or names, of the Fields(s) to be used as the basis for selecting the records. In this
example it is the column header. The second set of information is the actual field value(s) or
record(s), which are to be selected.

All database functions have the same syntax: Data Source, Field of Interest, Criteria. It is
important to understand that while you do not need to duplicate the column headings as was
done for the example above, copying the header row insures that the spelling is correct.



66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                      Page 19 of 23
This example uses two rows below the Criteria header for the actual criteria. The syntax is:
Row1 AND criteria 1 AND criteria 2 AND … Criteria N
OR
Row2 AND criteria 1 AND criteria 2 AND … Criteria N

To include multiple AND criteria for one column header, add that column header to end of the
row and type the additional criteria. Be sure to include the additional header and criteria in the
formula.

SUMPRODUCT
SUMPRODUCT improves on SUMIF by allowing the use of multiple ranges or additional criteria.
SUMPRODUCT works using the Array principle but does not require being array entered. It is
different from other Functions in that the ranges are separated either by an asterisk or a plus
instead of a comma. Normal algebraic signs apply to the ranges. For example, a Range could be
equal to, less than, greater than, etc. to a value or other field reference.

The formula is written:

=SUMPRODUCT((Range1)*(Range2)*…(Range29)*The Range To Sum)

where:

* means AND
+ means OR

Note: Range sizes must be the same for each range. If you are using a prompt field instead of a
field within the range then the formats must be the same.

In Excel 2007 SUMIFS performs the same operation but was specifically designed to be a
function rather than being adapted from an Array.

If you do not include “The Range To Sum” then the answer will be returned as a Count.

Example:




=SUMPRODUCT((dept=”OSC15910”)*(fund=”11000”)*(acct=”46200”)*amount)

The columns (dept, fund, acct, amount) are named ranges.
The algebraic symbol is a qualifier (e.g., =, >, <, >=, <=)
The criteria (in double quotes) can be entered as the content of the field (“OSC15910”), a field
within the table (B5), or a field range located outside of the table(see example below.




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                     Page 20 of 23
                               This method is preferred since it serves to document your
conditions for an outsider reviewer to see.




Note: SUMPRODUCT usually works on arrays of numbers, but we can have arrays of
TRUE/FALSE here as well as an array of numbers. By using the '*' (multiply) operator, we can
get numeric values that can be summed. '*' has the effect of coercing these two arrays into a
single array of 1/0 values. Multiplying TRUE by TRUE returns 1 (try it, enter =TRUE*TRUE in a
cell and see the result), any other combination returns 0. Therefore, when both conditions are
satisfied, we get a 1, whereas if any or both conditions are not satisfied, we get a 0.

Multiplying the first array of TRUE/FALSE values by the second array of TRUE/FALSE values
returns a composite array of 1/0 values, or {0,0,1,1,0,1,1,0,1,1,1,1}. This new array of 1/0 values
is then multiplied by the array of numbers sold to give another array of numbers that satisfy the
two test conditions. SUMPRODUCT then sums the members of this array to give us a count
which compared to a Sum column to give a correct answer for the conditions supplied.




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                     Page 21 of 23
Array Formulae

SUMPRODUCT is a multi-condition formula that was specifically designed to behave like an
Array formula even as it does not require array entry (Ctl-Shift-Enter). Array formulae at their
most basic allow the user to analyze two or more columns (called single arrays) of data to pick
out selected information that meet the conditions that have been established. While arrays are
certainly more powerful than what is being described here, our purpose is only to introduce the
subject.

Array formulae are distinguished from other formulae by the method of entry and the method
of display. The method of entry requires the user to initiate the formula by pressing Ctl-Shift-
Enter instead of just pressing Enter. Once Ctl-Shift-Enter is pressed, the cell reference remains
                                                    the same and does not advance to a new row
                                                    as it would if you just pressed Enter. If you
                                                    were to press just the Enter key on an Array
                                                    formula you would receive a #VALUE error. If
                                                    this happens: highlight the formula and press
                                                    Ctl-Shift-Enter.

                                                    An Array entered formula will show braces, {
                                                    }, around the formula. These braces are
                                                    created by MS Excel and cannot be entered
by the user (there are exceptions to this rule, which you will discover as you learn more about
the use of arrays). For the beginning Array user, the braces will indicate Array entry.

Concept
What you enter as a formula
{=SUM(IF((Prods="hats")*(Colors="white"),Units,””))}

Excel Sees a Matrix of True and False
{=SUM(IF(({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*(
{TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE; FALSE;FALSE}),Units,””))}

How that is translated by Excel to 1’s and 0’s
{=SUM(IF({1;1;1;1;0;0;0;0;0;0;0;0;0},Units,))}

Because
=TRUE*TRUE = 1
=FALSE*FALSE= 0
=TRUE*FALSE= 0




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                    Page 22 of 23
Once Excel has made the TRUE * TRUE calculations, it then multiples the arrayed answer by
what is being summed to create an answer set, which is then summed to give the answer the
user is looking for.

In the example above (matrix shown below), Excel is being asked to determine the number of
units where the product is ‘hats’ and the color is ‘white’.

  Prods    Colors                    Multiply   Units   Total


Hats      White     TRUE        1       X           7      7
Hats      White     TRUE        1       X           5      5
Hats      White     TRUE        1       X           4      4
Hats      White     TRUE        1       X           7      7
Hats      Blue      FALSE       0       X           4      0
Hats      Blue      FALSE       0       X           8      0
Hats      Blue      FALSE       0       X           6      0
Coats     White     FALSE       0       X           1      0
Coats     White     FALSE       0       X           1      0
Coats     White     FALSE       0       X           5      0
Coats     Blue      FALSE       0       X           2      0
Coats     Blue      FALSE       0       X           5      0
Coats     Blue      FALSE       0       X           3      0
                                                          23

Examples of Array entered formulae




66e3d2ce-165c-458b-a06b-0e7b25f1b99c.doc                                Page 23 of 23

								
To top