Chapter 2 Database Analytics

Data set

a collection of data

• ex: marketing surgery responses, a table of historical stock prices, and a collection of measurements of dimensions of a manufactured item

Database

a collection of related files containing records on people, places, or things

• A database file is usually organized in a two-dimensional table, where the columns correspond to each individual element of data (called fields, or attributes), and the rows represent records of related data elements

Excel table

allows you to use table references to perform basic calculations. Select the data range including headers.

• The table name (default: Table1), can be found (can changed) in the Properties group of the Table Tools Design tab in Windows or in the Table tab on a Mac. 

Filtering

is finding a subset of records that meet certain characteristics

• AutoFilter for simple criteria

• Advanced Filter for more complex criteria.

The filter tool does not extract the records; it simply hides the records that don’t match the criteria. 

Database functions

start with a “D” (ex: DSUM, DAVERAGE, DCOUNT) and allow you to specify criteria that limit the calculations to a subset of records using the same format as the Advanced Filter.

IF function

=IF(condition, value if true, value if false)

returns one value if the condition is true and another if the condition is false.

Conditions may include the following:

• = equal

• > greater than

• < less than

• <> not equal to

• >= greater than or equal to

• <= less than or equal to

AND function

=AND(condition1, conditioin2,…)

returns TRUE if all conditions are true and FALSE if not

OR function

=OR(condition1, condition2,…)

returns TRUE if any condition is true and FALSE if not.

Nesting IF functions

=IF(A8= 2, (IF(B3= 5, “YES”, ““)), 15)

VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, [range lookup])

looks up a value in the leftmost column of a table and returns a value in the same row from a column you specify

HLOOKUP

=HLOOKUP(lookup_value, table_array, row_index_num, [range lookup])

looks up a value in the top row of a table and returns a value in the same column from a row you specify

INDEX

=INDEX(array, row_num, col_num)

returns a value or reference of the cell at the intersection of a particular row and column in a given range.

MATCH

=MATCH(lookup_value, lookup_array, match_type)

returns the relative position of an item in an array that matches a specified value in a specified order.

Form Controls

buttons, boxes, and other mechanisms for inputting or changing data on spreadsheets easily that can be used to design user-friendly spreadsheets.

• Spin button: a button used to increase or decrease a numerical value

• Scroll bar: a slider used to change a numerical value

• Check box: a box used to select or deselect a scenario

• Option button: a radio button used to select an option

• List box: a box that provides a list of options

• Combo box: a box that provides an expandable list of options

• Group box: a box that can hold a group of controls

CHOOSE

=CHOOSE(index_num, value1. value2,…)

returns a value from a list based on the position in the list, specified by index_num.

Important notes on Lookup functions

• In the VLOOKUP and HLOOKUP functions, range lookup is optional. If this is omitted or set as True, then the first column of he table must be sorted in ascending numerical order.

• If an exact match for the lookup_value is found in the first column, then Excel will return the value of col_index_num of that row. If an exact match is not found, Excel will choose the row with the largest value in the first column that is less than the lookup_value

• If range lookup is False, then Excel seeks an exact match in the first column of the table range. If no exact match is found, Excel will return #N/A (not available).
  

  • Specify the range lookup to avoid errors!

PivotTables

allows you to create custom summaries and charts of key information in the data. Can be used to quickly create cross-tabulations and to drill down into a large set of data in numerous ways

Slicers

are tools for drilling down to “slice” a PivotTable and display a subset of data.