false

Study Guides
(248,168)

Canada
(121,359)

University of Ottawa
(9,916)

Administration
(1,202)

ADM1370
(79)

Craig Kuziemsky
(25)

Quiz

Unlock Document

Administration

ADM1370

Craig Kuziemsky

Winter

Description

Summary of Excel Examples
Why Learn Excel?
o Excel is the most popular spreadsheet tool today.
o Excel has been regarded as the true “killer app” by many experts.
o You probably already use or will soon be using Excel at work, and possibly at
home.
o Excel can be used for simple data management and problem-solving, as well as
complex decision making.
o From a Learning Standpoint:
o Excel can be used to cultivate
Critical Thinking and Decision Making Skills
Based on Robert Reich
(former US Secretary of Labour):
o Four Skills required for future job roles:
o A good Information Systems education
is comprehensive across these skills. Intro
Microsoft Excel
o a computer program
o used to enter, present, and analyze data
o Functions:
Data management
automatic calculations
presentation tools
decision analysis functions
Worksheet
o a spreadsheet
o collection of text & numbers laid out in a grid.
o Chartsheet for charts.
Workbook – collection of related worksheets
Quick Hacks
Select all – left hand corner triangle on spreadsheet
Status bar – at bottom of spreadsheet, customize by right clicking
Use cell references where possible
o Absolute (F4 fixes the cell to always refer to it. J2*I2F4 --> fixes so that all
numbers multiplied in J are only multiplied by I2.) i.e. G$2$
o Relative i.e. G2
o Mixed
$B6, fixes column (column is fixed to B, but the row can increase or
decrease as the formula is copied vertically)
J$11, fixes row (column can vary as the formula is copied horizontally but
the row is fixed to 11) Select non-contiguous range (hold ctrl)
Press F2 to type in cell
Use the help engine
Ctrl + a to open dialogue box
Autofill:
Highlight, click on lower right corner and drag down.
Excel doesn't have a built in list for the alphabet so it will only autofill what you have already
filled in. To create custom list of letters, go to file --> options --> advanced -->at the bottom click
edit custom lists --> highlight new list and enter list on the right column with the alphabet -->
add--> now the list is available
Formulas
Performs operations on literal or referenced values
Arithmetic operations
o Addition (+)
o Subtraction (-)
o Multiplication (*)
o Division (/)
o Exponentiation (^)
Complex Formulas
o Multiplication and division will automatically be performed before
addition/subtraction unless brackets around the addition/subtraction
o A1/B1*C1 – div. Before mult
o A1/(B1*C1) – mult. Before division.
Copying & Pasting Formulas
o Excel adjusts relative location of referenced cells to new position of pasted
formula o Paste Special
Home paste arrow past special values only, etc
o Can also use paste special to transpose columns to rows
Formula View
o Ctrl + ~ to view formulas instead of #s in workbook
o Can do the same by clicking tab Formulas show formulas
Tips:
o Don’t embed important data in a formula, reference to it instead
o Keep formulas simple
o Can break formulas into intermediate results
Common Formulas
= SUM(A1:A5)
o Sum of all values
=SUMPRODUCT(A1:A5,B1:B5)
o Multiplies A1*B1 and the rest by each other and sums them together
=AVERAGE(A1:A5)
o Average of these cells
=STDEV(A1:A5)
o how widely values dispersed from average
MAX(A1:A5)
o Maximum of the values
MEDIAN(A1:A5)
o The middle number of the values
PEARSON(A1:A5)
o Extent of linear relationship (-1 to 1) COUNT(A1:A5)
o Counts # of cells with numbers
COUNTA(A1:A5)
o Counts # of cells that are not empty
COUNTBLANK(A1:A5)
o Counts # of cells that are empty
COUNTIF(A1:A5,criteria)
o Counts all cells containing a certain criteria
Ranges & Excel Tables
Format as Excel Table
o Home --> Format as Table
Excel table back to range
o Table tools design convert to range
Use sorting arrows in excel table to filter/sort data
At the bottom of the excel table, you can insert a “Total Row”. Then, you can click on arrow
beside the total cell to get avg, sum etc.
Subtotals
- Before you can subtotal, make sure to sort by the field on which you want subtotalled
Grouping
- Organizing data so it can be viewed as collapsibles
- Data must be sorted by the column for that group
Autosum
Highlight cell and click AutoSum, it will guess what you want the sum of and click enter
Data Delivery Functions
Filter Unique Values
o Highlight column --> data --> advanced filter --> Copy to another location -->
unique values --> ok Data validation (Creates drop down menu):
o highlight cell --> data --> data validation --> dialogue box, click allow: value from
list --> source, type in the list manually or click on copied values --> ok
Lookup Functions
o =VLOOKUP(lookup_value,table_array,col_index_num[,range_lookup])
VLOOKUP(which cell to look up value for, from which table, from
which column in the table,FALSE for exact value)
o =HLOOKUP(lookup_value,table_array,row_index_num[,range_lookup])
o True – approx. match ex. %grade to letter grade
o False – exact match ex. Salary level to employee categorie
Phases in Problem Solving & Modeling Process
- Formulation (real problem mathematical problem)
o Identify problem & input data
o Develop model
- Solution (solving model)
o Analyze model
o Test result
- Interpretation (analyzing results & implementing solution)
o Present results & implement solution
o Perform sensitive analysis
Types of Models
- Mental (furniture arrangement)
- Visual (road maps)
- Physical (buildings)
- Mathematical (financial analysis) Benefits of Modeling
- Economy (less costly)
- Timeliness (quick)
- Feasibility (bring things that would be impossible)
Financial/Annuity Functions
- Annuity (series of constant cash payments over a continuous period)
- Cash you pay is represented as negative, cash you receive is represented as a positive
- Future Value Function
o =FV(rate, nper, pmt, pv, type)
o =FV(8%,10years,payment made each period,present value, when payment is
due 0 for end of period or 1 for beg. Of period)
Tips for using Formulas&Functions
- Formula Auditing
o Show formulas (Ctrl+~)
o Trace precedents (shows which cells were used for formula)
o Trace dependents (shows which other cells are dependent on the highlighted
cell)
- Heuristics-Based Modeling
o Maximax

More
Less
Related notes for ADM1370

Join OneClass

Access over 10 million pages of study

documents for 1.3 million courses.

Sign up

Join to view

Continue

Continue
OR

By registering, I agree to the
Terms
and
Privacy Policies

Already have an account?
Log in

Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.