Data Analytic by Excel(Intermediate level, 5 Sat) E001

You can either RSVP here or http://nycdatascience.com/course/excel-intermediate-level/

Date: April 26, May 3,10, 17, 31, 2014(5 Sat)

Time: 9:00am -1:00pm

Instructor: Mary DiPerna, Instructor at NYU

Cost: $850 for all five classes.

Venue:

http://photos3.meetupstatic.com/photos/event/6/d/b/2/600_353668082.jpeg

http://photos3.meetupstatic.com/photos/event/d/3/5/6/600_353694102.jpeg

Note:

We don't sell individual class. It is a big commitment for us to assist you and be able to do significant analytic work and also your commitment to do a good job in the class.

For group(5 or more persons) and enterprise pricing, please email vivian.zhang@supStat.com

It is preferred if you can paypal stoneapple@gmail.com (SupStat Inc business acct) to RSVP you seat and pay $1 on meetup.com since meetup charges 15% transaction fee.

Refund Policy:

We offer full refund if you are not happy with the first class and decide to drop it. Course Outline:

(Content may be adjusted based on the real teaching condition)

Multi-Sheet/File Operations: 4 hours

Abstract: describes how to work with complex Excel workbooks, learn how to set up and format more than one sheet simultaneously, create 3D and other multi-sheet formulas, and work with formulas linked to different Excel files.

Case studies and exercises: Creating multi-sheet files with multi-sheet/file formulas. Use techniques to maintain data integrity across files.

• Group Mode

• 3D formulas

• formulas with external references

• advanced Copy and Paste and absolute formulas

• Range Names

Data Referencing and Transformation: 2 hours

Abstract: use lookup functions to pull specific data from other sheets/files, learn inexact and exact match variations, use logical and information functions to flag and avoid calculation errors, use text transformation functions to transform data from other sources.

Case studies and exercises: Create formulas to pull data from different lists, use functions to flag data anomalies, use nested functions to transform data imported from other sources.

• Hlookup/Vlookup/Choose/Match reference functions

• If, IsErr, IsNA and IsBlank logical functions

• Find/Replace/Substitute /Left/Right/Mid string manipulation functions

Data Visualization: 2 hours

Abstract: create and modify Excel charts and learn when to use which type of visualization, learn to change legends, data points labels, titles, axis and data point formatting, as well as add data to existing charts .

Case studies and exercises: Create an overlay chart and data segmentation chart and add data to existing charts.

• Overlay chart

• Data segmentation chart

• Scatter chart with trend line

• Data Point

• Data Series

• Scale, Format, Alignment

• add data

Data Summarization: 6 hours

Abstract: maximize efficiency when using Excel as a database (Lists), highlight distribution, sort, filter, and subtotal, build data validation for more consistent data, create time-saving pivot tables and summary sheets that dynamically summarize subsets of information.

Case studies and exercises: Work with volumes of data and create data validation and median formulas that easily summarize different groupings within the data list. Create three different types of summaries using subtotals, functions and Pivot Tables.

• Conditional Formats

• Sort, Subtotal, Outline

• Filtering

• Data Validation

• Dget, Dsum, Dcount

• Combine If and Array formulas

• Pivot Tables

• Slicers

Data Analysis: 2 hours

Abstract: how to perform single and dual variable sensitivity analysis, find a specific target by varying input and perform What-if analysis and store multiple situations in a single file.

Case studies and exercises: Using an existing data analysis sheet, create single and dual variable sensitivity analyses. Use the scenario manager to maintain Best, Worst and Likely scenarios in a single file.

• One/Two variable Data Tables

• Autofill

• Goal Seek

• Scenario Manager

Data Automation: 4 hours

Abstract: record a macro to automate repeated actions, introduce Visual Basic to enhance recorded macros and broaden their functionality, practice using the Visual Basic debugger to step through and debug macros, incorporate decision making and looping commands that allow the macro to repeat.

Case studies and exercises: Record a simple macro and use VB debugger. Adjust the recorded macro to make it more flexible. Create a looping macro to transform volumes of information with a single click.

• Macro Recorder

• Absolute/Relative record

• Debugger

• Selection.Offset * Activecell * If * Do While MsgBox *

(If we finish the class early, we will cover selected topics based on your need)

Time-related functions: Abstract: use time related functions to calculate accurate work availably and monthly calculations, use time-value functions to determine current value of even and uneven streams of payment as well as internal rates of return. Case studies and exercises: Create formulas to accurately calculate workdays within a time period. Use time-value formulas to analyze the costs of various projects based on streams of payments and assumed interest rates.

• Edate * EoMonth * Workday * Weekday *

PMT * RATE * NPER

• PV * FV * NPV * XNPV

• IRR * XIRR *

Additional Shortcut Tools: Abstract: explore additional features for quickly formatting and changing lists and providing multiple reporting views in a single worksheet, use tools to trace errors and track dependencies.

Case studies and exercises: Convert an existing list to a table and add formulas and formats and de-dupe the list. Work with a multi-year spreadsheet and create monthly, quarterly and annual reports. Work with an existing file and trace formulas to identify possible errors.

• Tables

• Table Styles

• de-dupe a list

• Custom Views

• Advanced Printing options

• Select Special

• Trace precedents/dependents

• Circular References

Additional VBA:

Abstract: use techniques to interact with the user, learn about error trapping, learn additional looping commands as well as how to build structured macros with arguments that change the effect of the routine.

Case studies and exercises: Add routine to allow the user to select specific files and build error routines to trap when incorrect files are selected. Create a looping routine that can be called in different situations using varying arguments to change the result of the sub-routine. * On Error * Goto * Application.Dialogs * Option Explicit * Declare * For Next * Arguments *