M.I.S excel exam

One variable data table

a structured range that contains different values for one variable to compare how the different values affect one or more calculated results

Two variable data table

a structured range that contains different values for two variables to compare how these differing values affect the results for one calculated value

Goal seek

a tool that enables you to specify a desired result from a formula without knowing what input value achieves that goal

Why would you use a Profit Model or MCDM?

When choosing from list of alternatives that need to be evaluated based on a number of criteria, a profit model or MCDM (multi criteria decision model) will help determine a choice/decision.

IF Function

checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

syntax of IF function

\=IF(logical_test,value_if_true,value_if_false)

PMT Function

calculates the payment for a loan based on constant payments a constant interest rate

syntax of PMT function

\=PMT(rate,nper,pv,[fv],[type])

VLOOKUP Function

accepts a value and looks for the value in the left column of a specified table array and returns another value located in the same row from a specified column

syntax for VLOOKUP function

\=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

SUM Function

totals values in one or more cells and displays the result in the cell containing the function

syntax of SUM function

Syntax: \=SUM(number1,[number2]...)

MIN function

analyzes an argument list to determine the lowest value

syntax of MIN function

\=MIN(number1,[number2]...)

MAX function

analyzes an argument list to determine the highest value

syntax of MAX function

\=MAX(number1,[number2]...)

COUNT Function

tallies the number of cells in a range that contain values usable in calculations, like numerical and date data, but excludes blank cells or text entries from the tally

syntax of COUNT function

\=COUNT(value1,[value2])

SUMPRODUCT Function

returns the sum of the products of corresponding ranges or arrays

finds the midpoint value, which is the value that one half of the data set is above or below

syntax of SUMPRODUCT function

\=SUMPRODUCT(array1,array2,[array3]...)

MEDIAN function

finds the midpoint value, which is the value that one half of the data set is above or below

AVERAGE Function

calculates the arithmetic mean/average for the values in a range of cells

Ignores empty cells and cells containing N/A or text

syntax of AVERAGE function

\=AVERAGE(number1,[number2]...)

Relative Cell References

the default method of referencing in Excel

What does a cell reference indicate?

a cell's relative location from the original cell containing the formula

Absolute Cell References

provides a constant reference to a specific cell

What happens when copying a formula containing an absolute cell reference?

the cell reference in the copied formula does not change, regardless of where you copy the formula

Chart

a visual representation of numerical data that compares data and reveals trends or patterns to help people make informed decisions

Column Chart

compares values across categories (job titles) using vertical columns... the vertical axis displays values and the horizontal axis displays categories

When to use a column chart?

Most effective when they are limited to seven or fewer categories - if too many categories exist, the columns appear close together and make it difficult to read the labels

Clustered Column Chart

compares groups/clusters of columns set side by side

When to use a clustered column chart?

Facilitates quick comparisons across data series, and is effective for comparing several data points among categories

Stacked Column Chart

shows the relationship of individual data points to the whole category, it displays only one column for each category, and each category within the stacked column is color-coded for one data series... make sure data are additive, each column represents a sum of the data for each segment

when to use a stacked column chart?

Use to compare the total values across categories as well as to display the individual category values

100% stacked column chart

converts individual data points into percentages of the total value, similar to a pie chart... each data series is different color of the stack, representing a percentage

why to use 100% stacked column chart?

depicts contributions to the whole

Bar Chart

compares values across categories using horizontal bars - horizontal axis displays values & vertical axis displays categories (bar & column charts both compare categories of data)

When are bar charts preferable?

when category names are long because a bar chart enables category names to appear in an easy-to-read format

Line Chart

displays lines connecting data points to show trends over equal time periods where the category axis (X-axis) represents time and the value axis (Y-axis) represents a value (like money or quantity). Excel displays each data series with a different line color

Why are line charts beneficial?

Enables you to detect trends because line continues to the next data point

Pie Chart

shows each data point as a proportion to the whole data series, it displays as a circle/pie where the entire pie represents the total value of the data series and each slice represents a single data point (limit a pie chart to about 7 data points)

Why use a pie chart?

when you want to convey percentage

Combo chart

combines two chart types (such as column and line) to plot different data types (such as values and percentages), and has a primary axis which displays on the left of the chart and a secondary axis that displays on the right side

Why use a combo chart?

useful to show 2 different but related data types

X Y Scatter Chart

shows a relationship between two numerical variables using their X and Y coordinates. Excel plots one variable on horizontal X-axis & other variable on the vertical Y-axis

Why use XY Scatter Chart?

to represent data in educational, scientific, and medical experiements

Sparkline

a small line, column, or win/loss chart contained in a single cell and its purpose is to present a condensed, simple, succinct visual illustration of data

Why use sparklines? (2 reasons)

Inserting next to data helps create a visual "dashboard" to help you understand the data quickly without having to look at a full-scale chart

Use to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values

Data Validation

allows the user to set the values that can be allowed in any given cell, it creates a drop down list of values (like in Billy's Buffet with the buffet price)

Conditional Formatting

applies special formatting to highlight or emphasize cells that meet specific conditions

if the condition is true for conditional formatting

Excel formats the cell automatically based on that condition and if the condition is false, Excel does not format that cell (Similar to IF function)

conditional formatting options

highlight cells rule

top/bottom rule

data bars

color scales

icon sets

conditional formatting options in quick analysis

text contains

duplicate values

unique values

equal to

clear format

records

each row in a table, which is a collection of related data about one entity

each record needs to be:

unique, and don't leave blank rows between records or between the field names and the first record

fields

each column in a table, which is an individual piece of data

total row

displays below the last row of records in an Excel table and enables you to display summary statistics (such as a sum of values displayed in a column)

Structured References

tag or use of a table element (field heading) as a reference in a formula

Sorting

the process of arranging records by the value of one or more fields within a table, it's not limited to data within tables: normal data ranges can be sorted as well

Sorting Multiple Fields

using multiple level sort enables like records in the primary sort to be further organized by additional sort levels

Custom Sorting

allows you to select and create how you want to sort the data

Filtering

the process of specifying conditions to display only those records that meet certain conditions

Types of Filters

text

number

data

custom

SUBTOTAL function

calculates an aggregate value, such as totals or averages, for displayed values in a range, table, or database

syntax of SUBTOTAL

\=SUBTOTAL(function_num.ref1)

PivotTable

an interactive table that uses calculations to consolidate and summarize data from a data source into a separate tables

why use a PivotTable?

-to analyze data in dataset w/out altering dataset itself

-they're dynamic and easy to alter

-helps identify trends and patterns

PivotTable Fields List

a task pane that displays the list of fields in a dataset and areas to place the fields to create the layout to organize data in columns, rows, values, and filters in a PivotTable, displays on the right side of the worksheet

slicer

a small window containing one button for each unique item in a field so that you can filter the PivotTable quickly... the visual representation is easier to manipulate than adding more fields to the FILTERS area and then setting each field's filter

Calculated field in a pivottable

a user-defined field that derives its value based on performing calculations in other fields in a PivotTable ... the calculated field does NOT exist in the original data set

"show values as" options in calculated field of pivottable

% of grand total

% of column total

% of row total

% of parent row total

Running total

Rank: Small-Large

Rank: Large-Small

information system

set of interrelated components that collect, process, store, and distribute information to support decision-making in an organization

5 component model of information systems

hardware, software, data, procedures, people

hardware

the computer parts

software

instructions for hardware written in computer language, written by computer manager

data

input/raw material (bridge between info. tech and info. systems)

procedures

instructions for people