Events  Classes  Deals  Spaces  Jobs 
    Sign in  
 
 
Taught by Mary DiPerna (Instructor from NYU)
Saturday, April 26, 2014 at 09:00 AM    Cost: $850
Venue, 241 Centre St, 6th Fl
 
     
 
 
              

              
 
Sign up for our awesome New York
Tech Events weekly email newsletter.
   
 
LOCATION
 
DESCRIPTION
Please RSVP this class at http://nycdatascience.com/course/excel-intermediate-level/

Sign up for the newsletter for free Data Science learning material & upcoming classes at http://nycdatascience.com/

Sign up for NYC Open Data Meetup for free workshops twice/week!
http://www.meetup.com/NYC-Open-Data/


Date: April 26th, May 3rd, 10th, 17th, 24th (5 Saturdays)

Time: 9:00am - 1:00pm

Instructor: Mary DiPerna, Instructor from NYU

Venue (one of the following locations, we will confirm ASAP):
241 Centre Street, 6th floor, New York, NY (SOHO)
500 7th Ave, 17th floor, New York, NY (close to Times Square)

This five week, intermediate level, course goes from multi-sheet/file formulas to creating a looping macros that transform volumes of information with a single click.

Project Demo Day & Certificates

From the rudimentary building blocks of programming basics, to data manipulation & use of advanced drawing packages, the course ends with a demonstration of a project of your choice on Project Demo Day. On Demo Day you will access & analyze real data, utilizing the tools & skill sets taught to you throughout the course. After the successful completion of the course, you will qualify for one of three certificates: Extraordinary Standing pass, Honorable Graduation pass, & Active Participation pass. Certificates are awarded according to your understanding, skill, & participation.

Syllabus

4 hours: Multi-sheet/File Operations

How to work with complex Excel workbooks

Set up & format multiple sheets

3D formulas

Formulas with external references

Advanced Copy & Paste

Absolute formulas

Range Names

2 hours: Data Referencing & Transformation

Use lookup functions to pull specific data

Exact & inexact match variations - Hlookup, Vlookup, Choose, Match

Logical & information functions - If, IsErr, IsNA, IsBlanks

String manipulation functions - Find, Replace, Substitute, Left, Right, Mid

2 hours: Data Visualization

Create charts

Change legends, labels, titles, axes, & datapoint formating

Overlay chart

Data segmentation chart

Scatter chart with trend line

Adding data to charts

6 hours: Data Summarization

Maximize efficiency when using Excel as a database

Conditional Formats

Sort, Subtotal, Outline, Filtering

Dget, Dsum, Dcount

Pivot tables & summary sheets

Data validation

Combine If & Array formulas

Slivers

2 hours: Data Analysis

Single & dual variable sensitivity analysis

Finding targets by varying inputs

What-If analysis

Autofill

Goal Seek

Scenario Manager

4 hours: Data Automation

Record a macro to automate repreated actions

Macro Recorder

Absolute vs Relative Record
Use Visual Basic - enhance macros, using the debugger, decision making & looping commands

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

Time-related functions:

Use time related functions to calculate accurate work availably & monthly calculations, use time-value functions to determine current value of even & uneven streams of payment as well as internal rates of return. Case studies & 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 & assumed interest rates.

Additional Shortcut Tools:

Explore additional features for quickly formatting & changing lists & providing multiple reporting views in a single worksheet, use tools to trace errors & track dependencies. Case studies & exercises: Convert an existing list to a table & add formulas & formats & de-dupe the list. Work with a multi-year spreadsheet & create monthly, quarterly & annual reports. Work with an existing file & trace formulas to identify possible errors.

Additional VBA:

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 & exercises: Add routine to allow the user to select specific files & 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.

* If, IsErr, IsNA & IsBlank logical functions

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

Data Visualization: 2 hours

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

Case studies & exercises: Create an overlay chart & data segmentation chart & 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, & subtotal, build data validation for more consistent data, create time-saving pivot tables & summary sheets that dynamically summarize subsets of information.

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

* Conditional Formats

* Sort, Subtotal, Outline

* Filtering

* Data Validation

* Dget, Dsum, Dcount

* Combine If & Array formulas

* Pivot Tables

* Slicers

Data Analysis: 2 hours

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

Case studies & exercises: Using an existing data analysis sheet, create single & dual variable sensitivity analyses. Use the scenario manager to maintain Best, Worst & 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 & broaden their functionality, practice using the Visual Basic debugger to step through & debug macros, incorporate decision making & looping commands that allow the macro to repeat.

Case studies & exercises: Record a simple macro & 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 & monthly calculations, use time-value functions to determine current value of even & uneven streams of payment as well as internal rates of return. Case studies & 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 & 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 & changing lists & providing multiple reporting views in a single worksheet, use tools to trace errors & track dependencies.

Case studies & exercises: Convert an existing list to a table & add formulas & formats & de-dupe the list. Work with a multi-year spreadsheet & create monthly, quarterly & annual reports. Work with an existing file & 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 & exercises: Add routine to allow the user to select specific files & 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 *
 
 
 
 
© 2017 GarysGuide      About   Terms   Press   Feedback