Study Guides (248,168)
Canada (121,359)
Administration (1,202)
ADM1370 (79)

Notes leading up to Quiz 2.docx

14 Pages
Unlock Document

Craig Kuziemsky

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

Log In


Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
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.

Add your courses

Get notes from the top students in your class.