Advanced Excel Part 2 - Core-CT

Advanced Excel Part 2

Instructor Notes September 16, 2016

1) INDEX and MATCH ................................................................................................................................ 1 2) Dynamic Tables - Introduction .............................................................................................................. 3 3) SUMIFS using a Table ............................................................................................................................ 5 4) Running Totals (Run_Tot) ..................................................................................................................... 6 5) Dashboard / OFFSET.............................................................................................................................. 7 6) INDEX/MATCH: Dynamic Table ........................................................................................................... 10 7) SUMIF.................................................................................................................................................. 11 8) Array Formula ..................................................................................................................................... 12 9) Using INDEX/MATCH as an Array........................................................................................................ 13 10) SUMPRODUCT................................................................................................................................. 14 11) HLOOKUP ........................................................................................................................................ 15 12) Miscellaneous ................................................................................................................................. 16 13) FRP312 Formula .............................................................................................................................. 16

1) INDEX and MATCH

a. INDEX takes a defined block of data (usually a table) and returns a value to a cell based on coordinates 1. Syntax: =INDEX(Range,RowNumber,[ColumnNumber]) (1) [ ] indicates optional. 2. For example, if you have a 10 x 10 range and the function was =INDEX(range,1,1) then INDEX would return the top left cell contents. =INDEX(range,10,10) would return the bottom right cell contents. 3. When setting up a range, do not include row or column headers. A range is just the data. (1) A range can be a Named Range or a cell reference (ex. A2:F11) 4. INDEX is used most often in conjunction with MATCH

b. MATCH looks for a criteria in a list and returns a number indicating the location in the list. 1. Syntax: MATCH(LookUp,Range,[-1,0,1]) (1) Where 0 = Exact Match (the same as FALSE in VLOOKUP)

1|Page

(2) Where 1 = Exact or next lowest value in the list (the same as TRUE in VLOOKUP).

The list is in descending order.

(3) Where -1 = Exact or next highest value in a list (where the list is in ascending

(reverse) order ~ the opposite use of TRUE)

(4) If the third argument is omitted, it will default to 1.

2. For example, you have a list (in order) of names {Tom, Dick, Harry}. The MATCH

function is =MATCH("Dick",list,0) and will return the number 2 indicating the

position in the list of "Dick".

3. The lookup can be hard coded into the function (ex., "Dick" ~ must be in quotes) or

it can be a reference to another cell (ex., A1). If the lookup is a number, it does not

need to be in quotes.

4. Because MATCH returns a number, We can combine MATCH with INDEX

c. Syntax

1. =INDEX(Range,MATCH(Lookup,Range,0),MATCH(Lookup2,Range2,0))

Row Number

Column Number

2. INDEX/MATCH can substitute for VLOOKUP

(1) INDEX/MATCH provides the ability to have the lookup column anywhere in the

data. You are not tied to a left-most column scenario as you are with VLOOKUP.

(2) For example, you have an employee table where the employee number is

column 1 and the employee LastName,FirstName is column 2. You want to find

the employee number from the name. Your function would be:

=INDEX(Column1:Column2,MATCH(LastName,FirstName,Column2,0),1). This

function reads: Index the two columns and find the name in the second column

and return the related value in the first column.

d. Example from Class

1. The first example [=INDEX(Range,1,1)] shows how INDEX works in its most basic

form

2. The second example [=INDEX(Range,B25,D25)] places the criteria outside of the

formula. Having your criteria outside of the formula is considered a best practice.

3. The third example introduces the MATCH function, and is meant to show that

MATCH returns the position number of the list being reviewed. When you change

the contents of cells B26 and D26, the INDEX formula in C30 becomes #VALUE!. This

is normal. Rewriting the INDEX/MATCH formula will resolve the error.

(1) In cell C31: =MATCH(B26,DeptID,0)

In cell C32: =MATCH(D26,Month,0)

(2) And finally, we combine INDEX and MATCH to use one formula:

In cell C30: =INDEX(Range,MATCH(B26,DeptID,0),MATCH(D26,Month,0))

4. See the Miscellaneous section for instructions on

(1) Creating worksheet specific named ranges

2|Page

(2) Creating drop down menus

2) Dynamic Tables - Introduction

a. Dynamic tables are a collection of rows and columns that the user wants to manage independently from other data. You can have multiple tables on the same worksheet.

b. There are three ways to create a dynamic table. Highlight the data and headers: 1. Ctl-T 2. Ctl-L 3. Navigation: Insert (Ribbon) > Table > Create Table (dialog box) > OK 4. The default style that is created includes a blue header row and alternating light blue and white stripes for the data. This style can be changed.

c. Tables, by default, are named Table1, Table2 etc. This can be changed by navigating to the Design ribbon, highlighting the table name and renaming. Press Enter to complete the name change. 1. The naming rules are the same as for named ranges (1) No spaces (2) Letters, numbers, underscores, dots (3) Upper and lower case allowed but not necessary 2. Dynamic tables are listed in the Name Box drop down 3. Dynamic tables are included in workbook's function library and are distinguished from other functions by the use of the symbol. 4. Dynamic tables are listed in the Name Manager: Formulas (ribbon) > Name Manager 5. Dynamic tables are not named ranges and cannot be found by pressing F3

d. The header row is always visible. As you scroll down, the header labels replace the familiar column names (A,B,etc) 1. The active cell must be inside the table for this to happen. If the active cell is outside of the table then the column labels will remain as normal.

e. Adding rows or columns automatically expand the table. f. Columns can be selected by mousing over the header until you see a downward pointing

arrow and left clicking 1. The header row can be selected by mousing over the left-most cell of the table until

you see a right-pointing arrow and left clicking. g. From the Design tab you can create a pivot table from the data

1. Navigation: Design (ribbon) > Summarize with PivotTable 2. You can also create a pivot table from: Insert (ribbon) > PivotTable 3. When you add a column or row the table automatically expands. This means that

the pivot table is not restricted to inserting columns or rows inside the table. You can add them to the ends and the pivot table will recognize them h. Calculations within a table use table nomenclature rather than cell nomenclature

3|Page

1. They are called Calculated Columns 2. Cell nomenclature: =J9-K9

Calculated Column nomenclature: =[@[Gross Amt]]-[@Discount]. This is called a structured reference (1) The @ symbol indicates "this row" (2) The other references refer to the column header name. (3) The formula will be the same for every row 3. As soon as a formula is entered it is copied to the other cells in the column (1) If the column has the wrong format then the column must be highlighted before

applying the format (formatting one cell will not copy to all of the other cells) 4. If you are referencing a constant outside of the table you must either:

(1) Lock the cell reference (F4) (2) Use a named cell (automatically locked reference) (3) A non-locked cell reference is a relative reference and when the calculated

column copies down, it will copy the relative reference appropriately. 5. As rows are added to the table the calculated column will automatically include the

new information. i. When a table is created it automatically includes an auto-filter. This auto-filter works in

exactly the same as the standard Excel auto-filter. 1. The auto-filter can be turned off: Data (ribbon) > Filter j. From the Design tab you can insert a Total Row (checkbox) 1. The default value is Sum 2. Each cell in the Total Row has a drop down menu associated with it and the choices

presented are the same as for =SUBTOTAL (1) Average (2) Count (3) Count Numbers (4) Max (5) Min (6) Sum (7) StdDev (Standard Deviation) (8) Var (Varience) (9) More Functions (This allows the user to create a custom formula) k. The first and last column can be formatted differently from the other table columns. l. If you no longer want a dynamic table, you can convert it back to just a data range 1. Navigation: Design (ribbon) > Convert to Range >Convert to Normal Range (dialog ? Yes/No) 2. A table converted to a range retains the formatting it had when it was a table.

4|Page

(1) Use Design > Table Styles > None to convert the format to a range style before you convert the table to a range.

m. Drawbacks 1. No absolute references in structured formulas (this can be huge) 2. No formulas in headers. When converting a range to a table, these are renamed. 3. @ThisRow, but no #PreviousRow 4. Only one formula per column 5. Formulas can be difficult to read (Microsoft claims structured formulas are easier to read).

3) SUMIFS using a Table

a. The objective of this lesson is to show how a range can be locked in a dynamic table

environment. The lesson asks you to find a number of totals based on values in four

different columns. Usually SUMIFS has a single column as a RangeToSum. To fill in the

matrix as shown without using a dynamic table would require four separate SUMIF

formulas. With dynamic tables you can write one formula and copy it across and down.

b. Background

1. The syntax for SUMIFS is =SUMIFS(RangeToSum,Rng1,Crit1,Rng2,Crit2)

(1) There are a minimum of 5 required arguments

2. Our formula is

=SUMIFS(INDEX(Discounts,,MATCH(B$1,Discounts[#Headers],0)),Discounts[[Vendor]

:[Vendor]],Vndr)

3. When we deconstruct the formula we find:

(1) =SUMIFS(INDEX(Discounts,,MATCH(B$1,Discounts[#Headers],0)),...

(2) Notice that the INDEX function skips the Row indicator (,,) and goes directly to

the column indicator: MATCH(B$1,Discounts[#Headers],0). This means that the

first three arguments are contained in this one expression.

(3) One way to read this is: Sum the columns of the Discounts table where the table

headers equal the matrix headers in the row above.

(4) The last part of the formula contains arguments 4 and 5 of the SUMIFS.

(a) ...Discounts[[Vendor]:[Vendor]],Vndr)

Argument 4

Argument 5

(b) Which tells the formula to lock on the Vendor column [[Vendor]:[Vendor]]

and Match to the named range called vndr

(5) The whole formula reads: Sum the columns of the Discounts table where the

headers equal the headers in the row above AND where the Vendor column

equals the named range called vndr.

5|Page

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download