Home
Illustrates how to Add-In the Data Analysis ToolPak in Excel. Excel statistics data analysis toolpak. Check out our brand-new Excel Statistics Text: https://www.amazon.com/dp/B076FNTZCV In the text we cover installing the Data Analysis ToolPak and much more. YouTube Channel: https://www.youtube.com/user/statisticsinstructor
Views: 250989 Quantitative Specialists
Download File: https://people.highline.edu/mgirvin/AllClasses/210Excel2013/Ch00/Excel2013StatisticsChapter00.xlsx All Excel Files for All Video files: http://people.highline.edu/mgirvin/excelisfun.htm. Intro To Excel: Store Raw Data, Data Types, Data Analysis, Formulas, PivotTables, Charts, Keyboards, Number Formatting, Data Analysis & More: (00:08) Introduction to class (00:49) Cells, Worksheets, Workbooks, File Names (02:54) Navigating Worksheets & Workbook (03:58) Navigation Keys (04:15) Keyboard move Active Sheet (05:40) Ribbon Tabs (06:25) Add buttons to Quick Access Tool Bar (07:40) What Excel does: Store Raw Data, Make Calculations, Data Analysis & Charting (08:55) Introduction to Data Analysis (10:37) Data Types in Excel: Text, Numbers, Boolean, Errors, Empty Cells (11:16) Keyboard Enter puts content in cell and move selected cell down (13:00) Data Type DEFAULT Alignments (13:11) First Formula. Entering Cell References in formulas (13:35) Keyboard Ctrl + Enter puts content in cell & keep cell selected (14:45) Why we don’t override DEFAULT Alignments (15:05) Keyboard Ctrl + Z is Undo (17:05) Proper Data Sets & Raw Data (24:21) How To Enter Data & Data Labels (24:21) Stylistic Formatting (26:35) AVERAGE Function (27:31) Format Formulas Differently than Raw Data (28:30) Keyboard Ctrl + C is Copy. Keyboard Ctrl + V is Paste (29:59) Use Eraser remove Formatting Only (29:19) Keyboard Ctrl + B adds Bold (29:57) Excel’s Golden Rule (31:43) Keyboard F2 puts cell in Edit Mode (32:01) Violating Excel’s Golden Rule (34:12) Arrow Keys to put cell references in formulas (35:40) Full Discussion about Formulas & Formulas Elements (37:22) SUM function Keyboard is Alt + = (38:22) Aggregate functions (38:50) Why we use ranges in functions (40:56) COUNT & COUNTA functions (42:47) Edit Formula & change cell references (44:18) Absolute & Relative Cell References (45:52) Use Delete Key, Not Right-click Delete (46:40) Fill Handle & Angry Rabbit to copy formula (47:41) Keyboard F4 Locks Cell Reference (make Absolute) (49:45) Keyboard Tab puts content in Cell and move selected Cell to right (50:55) Order of Operation error (52:17) Range Finder to find formula errors (52:34) Lock Cell Reference after you put cell in Edit Mode (53:58) Quickly copy an edited formula down a column (53:07) F2 key in last cell to find formula errors (54:15) Fix incorrect range in function (54:55) SQRT function & Fractional Exponents (57:20) STDEV.P function (58:10) Navigate Large Data Sets (58:48) Keyboard Ctrl + Arrow jumps to bottom of data set (59:42) Keyboard Ctrl + Shift + Arrow selects to bottom of data set (Current Range) (01:01:41) Keyboard Shift + Enter puts content in Cell and move selected Cell up (01:02:55) Counting with conditions or criteria: COUNTIFS function (01:03:43) Keyboard Ctrl + Backspace jumps back to Active Cell (01:05:31) Counting between an upper & lower limit with COUNTIFS (01:07:36) COUNTIFS copied down column (01:10:08) Joining Comparative Operator with Cell Reference in formula (01:12:50) Data Analysis features in Excel (01:13:44) Sorting (01:16:59) Filtering (01:20:39) Introduction to PivotTables (01:23:39) Create PivotTable dialog box (01:24:33) Dragging & dropping Fields to create PivotTable (01:25:31) Dragging Field to Row area creates a Unique List (01:26:17) Outline/Tabular Layout (01:27:00) Value Field Settings dialog to change: Number Formatting, Function, Name (01:28:12) 2nd & 3rd PivotTable examples (01:31:23) What is a Cross Tabulated Report? (01:33:04) Create Cross Tabulated Report w PivotTable (01:35:05) Show PivotTable Field List (01:36:48) How to Pivot the Report (01:37:50) Summarize Survey Data with PivotTable. (01:38:34) Keyboard Alt, N, V opens PivotTable dialog box (01:41:38) PivotTable with 3 calculations: COUNT, MAX & MIN (01:43:25) Count & Count Number calculations in a PivotTable (01:45:30) Excel 2013 Charts to Visually Articulate Quantitative Data (01:47:00) #1 Rule for Charts: No Chart Junk! (01:47:30) Explain chart types: Column, Bar, Pie, Line and X-Y Scatter Chart (01:51:34) Create Column Chart using Recommended Chart feature (01:53:00) Remove Field Buttons from Pivot Chart (01:54:10) Chart Formatting Task Pane (01:54:45) Vary Fill Color by point (01:55:15) Format Axis with Numbers by Formatting Source Data in PivotTable (01:56:02) Add Data Labels to Chart (01:57:28) Copy Chart & Create Bar Chart (01:57:48) Change Chart Type (01:58:15) Change Gap Width. (01:59:17) Create Pie Chart (01:59:23) Do NOT use 3-D Pie (01:59:42) Add % Data Labels to Pie Chart (02:00:25) Create Line Chart From PivotTable (02:01:20) Link Chart Tile to Cell (02:02:20) Move a Chart (02:02:33) Create an X-Y Scatter Chart (02:03:35) Add Axis Labels (02:05:27) Number Formatting to help save time (02:07:24) Number Formatting is a Façade (02:10:27) General Number Format (02:10:52) Percentage Number Formatting (02:14:03) Don’t Multiply Relative Frequency by 100 (02:17:27) Formula for % Change & End Amount
Views: 436491 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:21) What Is / Are Statistics? Define Statistics 2. (02:42) Descriptive Statistics and Inferential Statistics 3. (05:00) Collect, analyze and present raw data on Yahoo Stock Price: Search the internet, download, create formulas and crate a Chart and present the information in a useful way. 4. (06:05) Get Data from http://finance.yahoo.com/ 5. (06:43) Copy and paste data from internet into an Excel spreadsheet. Learn about Paste Special, Text 6. (07:28) Download a “.csv” file. “.csv” and “.txt” file types (file extensions) are text files. When you open them, they look like Excel, but it is not. We can copy and paste into a real Excel file. Text files are common files types that allow raw data to be transferred from one system to another. Text files are intermediate steps between the original Database and your Excel Spreadhseet. 7. (07:46) Control Panel, Folder Options, View Tab, Uncheck “Hide extensions for known file types” 8. (08:47) Highlight whole table keyboard is Ctrl + * 9. (09:50) Calculate maximum stock price with MAX function, minimum stock price with MIN and Mean using the AVERAGE function. 10. (11:30) Keyboard shortcuts for highlighting ranges that are not next to each other (noncontiguous ranges): Ctrl + Mouse Click 11. (12:15) Create Line Chart from Historical Data 12. (13:58) Page Setup 13. (17:52) Example of Inferential Statistics
Views: 39505 ExcelIsFun
This video will guide you through the installation of Analysis ToolPak in Excel on your PC.
Views: 16546 gscoic1
Views: 38607 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:09) Overview of Frequency Distributions for Quantitative Variable 2. (02:02) Create Frequency Distribution with PivotTable for Grade Data where NUMBERS ARE DECIMALS (important distinction for grouping feature in a PivotTable) 3. (03:08) Grouping Feature in a PivotTable for creating Classes or Categories for a Decimal Quantitative Variable. Class that are created are 0-10, 10-20, 20-30, etc. Extensive Discussion about how to create classes or categories that are NOT Ambiguous. 4. (05:03) Upper Limit for Class/Category is Not Included when the numbers are Decimals. 5. (05:58) Aggregate Function for Number Values defaults to Count when you have Grouped Numbers in the Row area of the PivotTable. 6. (06:32) Double Click PivotTable to Extract Records that match the criteria from the Row area of the PivotTable 7. (09:16) Use Find and Replace feature to create non-ambiguous labels in a Grouped Decimal Number PivotTable. 8. (10:20) Create Histogram for Quantitative Variable (Grouped Numbers) for Grade Data. This Histogram has Frequencies at the top of each column and the gap width is zero. The colors for each column are different. 9. (13:13) Create Frequency Distribution with PivotTable for Grade Data where numbers are WHOLE NUMBERS (important distinction for grouping feature in a PivotTable) 10. (14:33) Methods for determining Number of Classes and Class Width for a Quantitative Variable 11. (18:19) When grouping Whole Numbers in a PivotTable the classes that are created are not ambiguous. We get classes like: 16-22, 23-29, 30-36. Etc. 12. (20:07) Create Histogram for Quantitative Variable (Grouped Numbers) for Age Data. This Histogram has Frequencies in the vertical axis and the gap width is zero. The colors for each column are the same. 13. (22:00) Discussion about Skew, Histogram shape and Histogram distribution of column heights. 14. (25:37) Relative Frequency and Percent Frequency Distribution built with a PivotTable based on Age Data that is shown as a Whole Number. 15. (27:48) Formulas 16. (27:44) Create Frequency Distribution with Formulas for Grade Data. 17. (30:03) Text Formulas for Category Labels 18. (30:40) COUNTIFS function with Comparative Operators Joined to Lower and Upper Limits from the Cells. 19. (33:17) Relative/Percent Frequency Formula. 20. (34:00) Create Histogram for Grade Data based on Frequency Distribution created with formulas. 21. (35:56) See that we can change the categories be more precise when we use formulas. 22. (38:20) Link Data Labels in Chart to cells in the spreadsheet 23. (39:16) See how formulas allow Frequency Distribution Formulas and Histogram Chart update automatically when raw data change. See different grade distributions with Histogram. 24. (40:45) Summary
Views: 166943 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm This video covers how to create Frequency Distributions and Appropriate Charts for Categorical Data using Formulas, PivotTables, Column Charts, Bar Charts and Pie Charts: 1. (00:32) Overview with diagram of tables and charts we will create in this video 2. (01:01) Notes in Workbook that you can print out and read 3. (02:05) Define Frequency Distribution 4. (03:55) Frequency Distribution create from Cell Phone Sales Data 5. (04:00) Advanced Filter to get a Unique List for Frequency Distribution 6. (05:55) Using COUNTIFS function to Create Frequency Distribution 7. (07:00) Formulas to create Relative Frequency 8. (09:36) TRUNC function to show that Number Formatting does NOT remove decimals 9. (11:00) Why Relative Frequencies add up to 1: Collectively Exhaustive and Mutually Exclusive Categories 10. (11:20) Formulas to create Percent Frequency with Number Formatting 11. (13:24) Formulas to create Percent Frequency with Times 100 12. (14:54) Add Borders using Format Cells Dialog Box: Solid Top Border and Double Bottom Border 13. (16:38) Use PivotTable to create Frequency Distribution 14. (17:38) Create Relative Frequency & Percent Frequency using the “Show Values As” feature and the “% of Column Total” option 15. (19:30) Compare and Contrast Formulas and PivotTables 16. (21:09) Column Chart for Frequency Distribution with Categorical Data 17. (23:59) Column Chart for Percent Frequency Distribution with Categorical Data 18. (25:10) Bar Chart for Frequency Distribution with Categorical Data 19. (25:44) Pie Chart for Percent Frequency Distribution with Categorical Data 20. (27:30) Chart from PivotTable with Many Columns of Calculations (Trouble) 21. (28:57) Create Individual PivotTables and PivotCharts to solve “Trouble”. Frequency Distribution create from Boomerang Product Sales Data. 22. (31:11) Discrete Quantitative Data (Numbers) should be charted with a Column that has Gaps. 23. (31:55) Pareto Chart Tabular & Graphical Displays For Categorical Variables
Views: 49183 ExcelIsFun
Views: 49306 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:43) Categorical Data vs. Quantitative Data 2. (02:00) Scales of Measurement (Levels of Measurement): Nominal, Ordinal, Interval, Ratio 3. (14:42) Cross Sectional Data vs. Time Series Data 4. (15:48) Graphical Display of types of Data 5. (16:22) How to Enter Data into the spreadsheet and use the Auto Complete (Auto Text) to your benefit 6. (18:50) How to create a new Excel Workbook to do your Homework from the Textbook
Views: 35948 ExcelIsFun
Views: 85 Maydina rini
This brief video shows how to load the Data Analysis Add-in in Microsoft Excel 2016. Same procedure applies in Excel 2013.
Views: 20627 Joshua Emmanuel
Download Excel Files: Start File: https://people.highline.edu/mgirvin/AllClasses/210M/Content/ch00/Busn210Ch00IntroToExcel.xlsm Finished File: https://people.highline.edu/mgirvin/AllClasses/210M/Content/ch00/Busn210Ch00IntroToExcelFinished.xlsm Full Page With All File Links: http://people.highline.edu/mgirvin/excelisfun.htm See how to Install Data Analysis Add-in using Excel Options and the Add-in Feature. This is a beginning to end video series for the Business & Economics Statistics/Excel class, Busn 210 at Highline Community College taught by Michael Gel ExcelIsFun Girvin
Views: 229467 ExcelIsFun
Views: 8356 David Dunaetz
Views: 177128 Excel Campus - Jon
Views: 116035 BIA 2610 and 3621
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:12) Intro to Exponential Probability Distribution 2. (03:05) Average time between Arrivals at Dick’s Hamburger Restaurant example with EXPON.DIST function 3. (07:02) Average time to wait in a Disney Line example using EXPON.DIST function and formulas.
Views: 6575 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:09) Accounting Example with Expected Value and Standard Deviation calculated from a Discrete Probability Distribution 2. (04:48) Finance Example with Expected Value and Standard Deviation calculated from a Discrete Probability Distribution
Views: 11411 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm This video covers skew in a distribution of data points and the SKEW function.
Views: 8403 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:16) Over View of File Import and Histogram Creation 2. (00:56) Look at Zipped Folder from class download then unzip it with Right-click, “Extract All” 3. (01:15) Text Files for communication between databases and data analysis programs like Excel 4. (02:06) Use Power Query to Import Multiple Files 5. (02:10) Get External Data Tab in Power Query, From File Button, From Folder Button 6. (02:33) We only need to keep “Content” Column, so right-click “Content” Field Name and point to “Remove Other Columns” 7. (02:51) To reveal data in imported tables, click the button with the Two Downward Point Arrows. 8. (02:58) Filter out Field Name. 9. (04:10) Name Query 10. (04:17) Close and Load To a cell in our worksheet (this brings table of data from the Power Query editor window into our worksheet) 11. (04:51) Build Frequency Distribution with a PivotTable 12. (05:28) Use Find and Replace feature to create non-ambiguous labels in a Grouped Decimal Number PivotTable. 13. (06:31) Add a Slicer for the Year Variable to the PivotTable 14. (07:26) Create Histogram
Views: 13934 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:11) Using Handwritten notes, discuss characteristics of the Normal (Bell) Distribution, how to calculate probabilities using Excel functions and the four situations for calculating probabilities and x or z values. 2. (12:18) Excel Chart Example for family of Normal (Bell) Curves. 3. (15:04) Calculate probability on the low end of the curve using NORM.DIST function with x values and NORM.S.DIST function with z values. 4. (17:50) Calculate probability on the high end of the curve using NORM.DIST function with x values and NORM.S.DIST function with z values. 5. (19:22) Calculate probability between two values using NORM.DIST function with x values and NORM.S.DIST function with z values. 6. (21:51) Find x values using NORM.INV function function or find z value using NORM.S.INV function
Views: 29262 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:24) Look at Data Set that exhibits the characteristics of a Poisson Experiment and compare it to the results of the POISSON.DIST to helps us understand why when can use this function to make probability predictions. 2. (05:57) Chart a POISSON Distribution with a column chart 3. (09:16) Discussion of POISSON experiment and POISSON.DIST Discrete Probability Distribution 4. (12:23) Use POISSON.DIST for arrivals at Dick’s Hamburger Restaurant 5. (17:14) Use POISSON.DIST for arrivals at an Accounting Tutoring Center 6. (18:20) Use POISSON.DIST for water line breaks in a 100 Mile stretch of pipe
Views: 8783 ExcelIsFun
Views: 4553 FREE LEARNING
Download files: https://people.highline.edu/mgirvin/AllClasses/216_2013/Content/04Excel/Excel2013.htm This video teaches: 1. Proper Data Set (00:50 min) 2. Data Analysis (01:04 min) 3. What does a PivotTable do? (01:20 min) 4. PivotTable Field List (02:39 min) 5. Visualize PivotTable before you start (02:58 min) 6. Create Pivot with one condition (01:59) 7. PivotTable keyboard: Alt, N, V (02:14 min) 8. Value Field Settings to add Number Formatting (so format follows the field if you Pivot) (05:48 min) 9. Design, Report Layout, Show in Tabular Form (07:21 min) 10. Create Cross Tabulated PivotTable (08:29 min) 11. Show empty cells as zeroes (11:05 min) 12. Pivot, Filter and Change Function in a PivotTable (12:05 min) 13. Three Functions (Sum, Count Numbers and Average) (17:39 min) 14. Difference between Count and Count Numbers functions in a PivotTable (19:30 min) 15. Copy and Paste a PivotTable (21:06 min) 16. Drop Text Value in Values area and make calculation with Count function on text data (21:27 min) 17. Group by Month and Year (22:15 min) 18. Filre Area in a PivotTable (24:52 min) 19. Filter Slicer (26:25 min) 20. Filter Timeline (28:06 min) 21. Formulas vs. PivotTable (30:49 min) Highline Community College Busn 216 Computer Applications Class Office 2013 (Fun With Computers) taught by Michael Girvin. Excel Basics. Learn Excel, How To Excel. Excel 2013 Pivot Tables. Excel 2013 PivotTables
Views: 32203 ExcelIsFun
Download files: https://people.highline.edu/mgirvin/AllClasses/216_2013/Content/04Excel/Excel2013.htm This video teaches: 1. Data Analysis: Sort, Filter, PivotTable (00:21 min) 2. Proper Data Set required for Data Analysis features (01:20 min) 3. Single Cell selected BEFORE clicking Sort, or Filter, or PivotTable (01:37 min) 4. Sort A to Z for single column with button (02:20 min) 5. Sort Z to A for single column with button (03:11 min) 6. Sort Z to A on single column and have whole data set sort (03:31 min) 7. Sort data with NO FIELD NAME (04:56 min) 8. Sort by Color (05:47 min) 9. Add a second level of sorting in Dialog Box (07:22 min) 10. Reorder Sort in Sort dialog box (07:50 min) 11. Sort based on Multiple Columns. Sort whole data set based on ONE column (08:10 min) 12. Sort upon two columns with buttons (Major Sort Last) (08:41 min) 13. Sort upon two columns with Sort dialog box (Major Sort on Top) (10:44 min) 14. Sort data set based on three columns (12:44 min) 15. Filter (14:02 min) 16. Filter with Filter check box to Extract Records to a new location (14:48 min) 17. Date Filter to Extract Records to a new location (16:10 min) 18. Top 10 Number Filter to Extract Records to a new location (16:49 min) 19. Filter with three AND criteria to extract records to a new location (17:32 min) 20. Filter with two OR Criteria to extract records to a new location (19:23 min) 21. Filter and get zero records in your data set: 1) Incorrect Q or 2) There are no records that match (20:13 min) 22. Search Filter (21:31 min) 23. Excel Table feature: Totals row to make calculations based on a Filter or Criteria (22:07 min) 24. Add new record when you have a Total Row in your Excel Table (24:57 min) Excel Table Total Row to make calculations with Criteria or Conditions Highline Community College Busn 216 Computer Applications Class Office 2013 (Fun With Computers) taught by Michael Girvin. Excel Basics. Learn Excel, How To Excel.
Views: 40928 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:32) Introduction to averages as typical values that represent a single value that represents all the data points 2. (01:17) Introduction to Location 3. (03:55) Sample Statistics are point estimators for the population parameters 4. (05:28) Mean using AVERAGE function 5. (09:16) Median using sorting or the MEDIAN function 6. (15:54) Mode for Categorical data using COUNTIFS and MODE for quatitative data using MODE.SNGL. Discussion about Bi-modal and multiple modes and why using a PivotTable rather than MODE.SNGL is wise. 7. (21:42) Relationship between Mean, Median and Mode and skew.
Views: 11508 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video are about Variation, Dispersion, Spread in the Data and Standard Deviation: 1. (00:09) Three data sets and charts of each to illustrate the concept of Variability, or Dispersion, or Spread in the Data. 2. (00:20) Set 1 (Hours Worked per Week Data), we ask the question: How fairly does the mean represent its data points? 3. (02:37) Set 2 (Seattle and Burien Gas Prices), we ask the question: How volatile are Prices? 4. (03:17) Set 3 (Supplier Days To Fill Order Data), we ask the question: How reliable is a Supplier? 5. (04:37) Calculate Range: Max - Min 6. (05:50) Calculate Interquartile range: Quartile 3 – Quartile 1 = Middle 50% of the values 7. (07:17) Variance and Standard Deviation Math Formulas for populations and samples. 8. (08:13) Calculate Sample Standard Deviation Long Hand and with STDEV.S for Data Set 3 (Supplier Days To Fill Order Data), were we ask the question: How reliable is a Supplier? 9. (16:45) Alternative Formula for Sample Standard Deviation 10. (17:05) Mean Absolute Error Formula 11. (17:20) Calculate Sample Standard Deviation with STDEV.S for Data Set 1, (Hours Worked per Week Data) were we ask the question: How fairly does the mean represent its data points? 12. (18:06) Calculate Sample Standard Deviation with STDEV.S for Set 2 (Seattle and Burien Gas Prices), were we ask the question: How volatile are Prices? 13. (19:25) Coefficient of Variation 14. (23:25) Using Descriptive Statistics feature to quickly calculate Numerical Measures like Mean, Median, Mode, Standard Deviation and more 15. (24:52) X-Y Scatter Chart to Visually Portray Variation or Dispersion or Spread in the Data. 16. (25:09) Summary of topics in video
Views: 17772 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:11) Discussion about Binominal Experiments and Probability Distributions 2. (11:00)Example 1: Experiment is attempting four sales calls. This example show how to create the Binomial Distribution in Table and Column Chart. This example show the 5 possibilities for comparative operators while using the BINOM.DIST function. 3. (24:10) Example 2: Experiment concerns late airline flights. This example show the 5 possibilities for comparative operators while using the BINOM.DIST function. 4. (29:47) Example 3: Experiment concerns West Seattle Bridge “Stuck in Traffic”. 5. (31:39)Example 4: Experiment concerns Student’s probability of withdrawing from Accounting 202. This example show how to use two BINOM.DIST function to calculate probabilities between two values. 6. (35:24) As number of trials get large, distribution approaches a Bell Shape. As the probability approaches 0.5, the distribution approaches a Bell Shape.
Views: 12416 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:09) Discussion of Slope and y-intercept formulas 2. (03:30) X Y Scatter Chart / Plot 3. (05:00) Calculate Slope and Intercept 4. (09:55) Use equation to predict 5. (10:48) Example 2 for calculating Slope and Intercept
Views: 6784 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm This video covers how to create Frequency Distributions and Appropriate Charts for Continuous Quatitative Data using Formulas, PivotTables and Histogram Column Charts: 1. (00:33) Remove unnecessary zeros in vertical axis using Number Format 2. (01:15) Chart Labels defaulted to zero decimals and we wanted to show two decimals. We use Number Formatting for the Percentage Chart Labels and increase decimals. 3. (01:49) Fix Chart Label Overlap 4. (02:00) Remove Leader Lines 5. (02:20) Capitalization error in Chart Tile that in linked to cell. When we edited the cell content, all the Chart Titles update
Views: 16732 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:12) Discussion about Discrete Probability Distributions, Random Variables, Continuous Random Variables, Discrete Random Variables, Discrete Probability Functions, Steps for creating Discrete Probability Distributions and calculating Expected Value (Mean) and Standard Deviation from a Discrete Probability Distributions. 2. (12:50) Create Discrete Probability Distributions for Isaac’s Italian Restaurant where we need to calculate probabilities for the random variable “Number of Banquet Rooms Used in One Day 3. (15:14) Calculate Expected Value (Mean) from Discrete Probability Distributions using SUMPRODUCT. 4. (16:55) Test to see if Expected Value formula works by calculating Mean for original Data Set 5. (17:15) Calculate Standard Deviation from Discrete Probability Distributions Long Hand. 6. (20:32) Test to see if Standard Deviation formula works by calculating Standard Deviation for original Data Set using STDEV.P 7. (21:28) Calculate Standard Deviation from Discrete Probability Distributions in a single cell using SUMPRODUCT and SQRT functions 8. (24:49) Calculate Probabilities from Discrete Probability Distributions for Isaac’s Italian Restaurant where we need to calculate probabilities for the random variable “Number of Banquet Rooms Used in One Day 9. (26:12) Create Discrete Probability Distributions and calculate Expected Value and Standard Deviation and calculate probabilities from Discrete Probability Distributions For Dick’s Hamburger Drive In Customer Arrival Data
Views: 10112 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:12) Chapter explanation 2. (01:57) Discussion about Samples, Point Estimates and Sampling Error 3. (03:50) Why take samples rather than use all the population items? 4. (05:10) What do we use samples for? Answering Research Questions! 5. (06:01) Learn about Sample Population and Target Population 6. (08:58) What is a Frame? Frame = List of items in population 7. (10:58) Simple Random Sampling: use when you have a finite population where you can create a frame. 8. (11:33) Random Sampling: use when you have a finite population or on-going process where you cannot create a frame. Two important points must hold true before selecting n items in a random way: 1) All items must be from same population (Samples = Target) and 2) Items must be selected Independently (without bias) 9. (13:25) Define Simple Random Sampling and do a few tests in Excel to prove to ourselves that the definition makes sense. We will look at one sample and the sampling error, then we will list all possible samples and then calculate the probability that we could get any one sample. 10. (16:20) Use VLOOKUP to list all numbers for all possible samples 11. (17:58) Calculate Sample Mean, Xbar, for every one of the possible samples 12. (18:18) Calculate probability of each sample (part of definition of Simple Random Sample) 13. (19:30) Introduction to RAND function 14. (20:36) Create Simple Random Sample using RAND Excel function (0 greater than or equal to 15 digit Number less than 1), the Sort Feature and Copy & Paste. 15. (23:12) Create Simple Random Sample using RAND, SMALL and VLOOKUPO Excel functions 16. (25:53) Conclusion
Views: 12339 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:15) Review of different X-Y Scatter 2. (02:42) Add Xbar Line and Ybar Line to X Y Scatter Chart to help interpret how Covariance is calculated and why the math behind the calculation makes sense 3. (08:36) Formulas and Calculations for Covariance and Correlation Coefficient (Long Hand) 4. (12:38) COVARIANCE.S Excel function for calculation Sample Covariance 5. (13:41) Correlation Coefficient & PEARSON Excel function for calculation Correlation Coefficient 6. (16:11) CORREL Excel function for calculation Correlation Coefficient 7. (18:50) Summary
Views: 15574 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:09) Calculate z score. Z Score is the Number of Standard Deviations a particular value is away from the mean. Deviation divided by Standard Deviation formula or STANDARDIZE function. 2. (08:15) Chebshev’s Rule: 5 Examples 3. (14:03) Empirical Rule: 12 Examples
Views: 12715 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:12) Review Handwritten PDF Notes about Probability, Random Events, Sample Points, Sample Space, Count Rule For Size of Sample Space, Listing all Sample Points with Tree Diagram and Table, Methods of Probability such as Classical, Relative Frequency and Subjective, Requirements for Probability, Events and Probability of Events 2. (14:30) In Excel: Experiment of Flipping Coin Three Times: Find all Sample Points, Calculate Probabilities and practice finding Probabilities of Events by listing all the sample points and then adding all the probabilities for each Sample Point to get the Probability of the Event 3. (21:09) Frequency Distribution Built from Sample Space in order to find Probabilities for Events 4. (23:46) In Excel: Experiment of Rolling Two Die: Find all Sample Points, Calculate Probabilities and practice finding Probabilities of Events by listing all the sample points and then adding all the probabilities for each Sample Point to get the Probability of the Event
Views: 16130 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:13) Discuss Handwritten notes for Adding Laws For Probability and cover the terms: Venn Diagram, Complement, Union, Intersection. 2. (08:25) Excel: Counting for AND Events use COUNTIFS with Two Criteria 3. (09:40) Excel: Counting for OR Events that are Mutually Exclusive use multiple COUNTIFS with One Condition/Criteria and simply add them. P(A OR B) = COUNTIFS(range,A)+COUNTIFS(range,B). 4. (11:23)Excel: Counting for OR Events that are NOT Mutually Exclusive use multiple COUNTIFS with One Condition/Criteria and simply add them, then subtract a COUNTIFS with AND Criteria. P(A OR B) = COUNTIFS(range,A)+COUNTIFS(range,B) – COUNTIFS(rangeA,A,rangeB,B). 5. (15:26) Compliment Rule 6. (16:30) Adding Probabilities from Frequency Distribution 7. (17:38) Adding Probabilities from Cross Tabulated Table with % of Grand Totals (Joint Probability Table) 8. (21:32) Frequency Distributions contain Mutually Exclusive categories. Examples of OR Events and Complement Events 9. (22:57) Adding events that are not Mutually Exclusive when you have just the Marginal and AND (Joint) probabilities. How to add when you don’t have original data set, a frequency distribution or a Cross Tabulated Table.
Views: 8325 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:10) Introduction to Uniform Probability Distribution including a look at source data sets. 2. (02:24) Formulas for Uniform Probability Distribution 3. (05:10) Example on paper: calculating probability of waiting on hold on the phone for customer support 4. (07:00) Example with Excel: calculating probability of waiting on hold on the phone for customer support 5. (12:10) Golf Driving Example using Excel
Views: 6241 ExcelIsFun
Download Excel File #1: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1468.xlsm Download Excel File #2: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1468Finished.xlsm Download Zipped Folder: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1468TextFiles.zip Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm Buy excelisfun products: https://teespring.com/stores/excelisfun-store Learn about how Topics: 1. (00:16) Introduction 2. (00:51) Discussion about the two different Data Ribbon Tabs and Get & Transform groups that you may see in Excel 2016 3. (03:51) Example #1: Clean Data and create Excel PivotTable Report, then add new data and refresh report! 4. (17:06) Example #2: Transform Cross Tabulated Table into Proper Data Set 5. (21:50) Example #3: Import 1.2 Million Rows of Data from Text Files into Power Pivot and create a Data Model PivotTable, then add new data and refresh our report. 6. (22:34) Diagram to understand Data Model and Power Pivot 7. (46:46) Unhanded Exception Error. 8. (48:02) Summary Reference Video: Highline Excel 2016 Class 03: Data Analysis Fundamentals: PivotTables, Power Query & Data Model https://www.youtube.com/watch?v=Jzt-I4mt8sQ
Views: 263845 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:16) VLOOKUP to Add New Region Field to Table 2. (06:14) Create Cross Tabulation based on the Grouped Revenue Field and the Region Field (Quantitative and Categorical Variables) 3. (08:01) VLOOKUP to Add New Price Field to Table 4. (09:58) Revenue Formula 5. (10:48) Create Frequency Distribution based on the Grouped Revenue Field (Quantitative Variables)
Views: 10601 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:25) Dot Plot 2. (01:37) Stem and Leaf Charts Reference Videos: Excel 2010 Statistics #20: Stem and Leaf Chart with REPT and COUNTIFS functions https://www.youtube.com/watch?v=tvEgMBrPw-E Excel 2010 Statistics #19: Dot Plot Chart with REPT and COUNTIFS functions https://www.youtube.com/watch?v=GK5NgLzLmpc
Views: 12125 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:12) Compare and contrast the Discrete Probability Distribution to the Continuous Probability Distribution 2. (01:53) Whereas with Discrete Probability Distribution we can calculate probability of an exact x value and probability between two x values, with Continuous Probability Distribution we are only calculating probability between two x values. 3. (06:55) Briefly look at example for Normal Distribution, Uniform Distribution and Exponential Distribution.
Views: 4902 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:35) Remind ourselves of what we did in Chapter 7: Population Mean & Standard Deviation Known and we compared Sample Mean (Xbar) to Sampling Distribution of Xbar. 2. (02:20) What we will do in Chapter 8: Population Mean not known so we create Confidence Intervals from Sample Mean (Xbar) and Margin of Error and estimate the probability that the Population Mean will lie within our Confidence Interval. 3. (03:33) Compare Chapter 8 to Chapter 7 with Printer Cartridge Company Example. 4. (05:48) Look at Visual that shows 100 Confidence Intervals and how most Intervals contains Population Mean, but a small number do not. 5. (07:13) Examples when it is impossible to calculate Population Means and thus we must use Sample Means (Xbar) 6. (08:04) Definition of Confidence Interval and Level of Significance / Alpha (Level of risk that the Interval will not contain the population mean) 7. (09:09) Formulas for building Confidence Intervals
Views: 4669 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:10) What is Dot Plot 2. (00:58) Create Dot Plot 3. (01:20) Create Sequential Numbers 0 to 10 4. (01:40) Change Column Widths & Style Formatting 5. (02:15) Cell Chart Formula 6. (02:30) COUNTIFS to Count with one condition / Criteria 7. (03:04) REPT function to Repeat dots 8. (04:19) Cell Formatting Dialog Box for Aligning Dots in cell vertically 9. (05:02) Copy and Paste Cell Chart and Quick Ways to Edit and Change Cell Ranges in Formulas
Views: 12247 ExcelIsFun
Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm Buy excelisfun products: https://teespring.com/stores/excelisfun-store In this video learn about the fundamentals of Data Analysis and Business Intelligence in Excel 2016: Sort, Filter, PivotTables, Power Query, Power Pivot Data Model: 1. (00:05) Introduction 2. (01:39) Sort 3. (03:02) Sorting one column 4. (03:23) Sorting multiple columns 5. (09:06) Sorting Mixed Data 6. (10:50) Filter feature 7. (13:22) Filter Drop-down Arrows to see Unique List 8. (15:34) Filter Different Data Types 9. (18:30) Filter to Extract Records 10. (20:54) OR Logical Test (OR Criteria) Discussion 11. (22:52) AND Logical Test (AND Criteria) Discussion 12. (28:23) BETWEEN and NOT Criteria 13. (30:57) PivotTable. Discussion of Crosstabulated tables and PivotTables as “Calculations with Criteria”, both AND Criteria and OR Criteria. 14. (33:05) PivotTable Basics: 1) Drag and Drop Field Names to add criteria to PivotTable, 2) Cross Tabulated Table, 3) Layout Formatting, 4) Number Formatting 15. (37:41) Adding Slicers 16. (40:21) Creating a Custom Style for a PivotTable 17. (44:48) Name PivotTable 18. (46:12) Create PivotTable using “Summarize Values By”, which allows us to change the Aggregate Functions like: SUM, COUNT, AVERAGE. 19. (51:49) Group Dates by Month and Year 20. (54:50) Create PivotTable using “Show Values As” to calculate “% of Column Total”. 21. (56:09) Hide items in Slicer 22. (57:24) Connect Multiple Slicers to Multiple PivotTables. 23. (58:57) Sort in PivotTable. 24. (01:00:11) Create multiple PivotTables with a single click using “Show Report Filter Pages” 25. (01:03:10) Why we need Power Query and Power Pivot Data Model 26. (01:05:21) Introduction to Power Query (Get & Transform) 27. (01:07:04) Power Query Example 1: Clean and Transform Data Table, Create PivotTable Based on Power Query Update, 3) Add new data to table and Refresh to update Query and PivotTable 28. (01:18:25) Power Query to Unpivot a Crosstabulated Table into a Proper Data Set. 29. (01:24:54) Introduction to Power Pivot and the Data Model 30. (01:25:32) Power Query to import multiple Text File tables with over one million records combine them into a single Table. We will use the “From File, From Folder” option. 31. (01:33:20) Load the million records in Power Query into the Power Pivot Data Model. 32. (01:34:42) Add an Excel Table into the Power Pivot Data Model 33. (01:35:30) Update Power Query 34. (01:36:36) Build a relationship between tables in the Power Pivot Data Model. 35. (01:38:08) Build PivotTable from Millions of Records from Two Tables 36. (01:40:34) Add new Text File to Folder and Update PivotTable. 37. (01:41:22) Summary
Views: 270321 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:25) Conditional Probability (3 Examples) 2. (10:30) Rule of Independence 3. (12:13) Multiplying Law of Probability 4. (13:49) Multiplying Rule for Events that are NOT Independent, One Sample Point. Multiplication Law for calculating probability of pulling three straight queens 5. (17:06) Multiplying Rule for Events that are NOT Independent, One Sample Point. Multiplication Law for calculating probability of pulling Queen, Not Queen, Queen (calculating probability for single Sample Point). 6. (18:40) Multiplying Rule for Events that are NOT Independent, More Than One Sample Point. Multiplication and Adding rules to calculate Probability of the Event “pulling two Queens in Three Tries” by calculating the probability for each sample point, then adding to get the probability for the Event. 7. (21:10) Multiplying Rule for Events that are NOT Independent, More Than One Sample Point. Calculating Conditional Probability using HYPGEOM.DIST function. 8. (23:00) Multiplying Rule for Events that ARE Independent, One Sample Point. (2 Examples) 9. (24:24) Multiplying Rule for Events that ARE Independent, More Than One Sample Point by calculating all the Sample Points than adding to get the probability of the Event. 10. (26:00) Multiplying Rule for Events that ARE Independent, More Than One Sample Point with BINOM.DIST function. 11. (27:00) Calculate Conditional Probability using the Multiplication Rule 12. (28:40) Calculate AND, OR and Conditional Probability From Cross Tabulated Tables and Joint Probability Tables. 13. (32:30) Summary
Views: 6345 ExcelIsFun
Views: 14672 Dwight Stoll
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:09) Five Number Summary: Min, Quartile 1, Quartile 2, Quartile 3 and Max 2. (02:41) Box Plots: Visual Summary of the Five Number Summary. How to calculate numbers with Excel and draw chart on paper 3. (07:55) +/- 3 Standard Deviation Outliers 4. (08:37) ABS function to find outliers 5. (09:17) +/- 3 Standard Deviation Outliers, COUNTIFS to count TRUE values 6. (10:08) +/- 3 Standard Deviation Outliers, Conditional Formatting to highlight Outliers
Views: 14689 ExcelIsFun
Visit http://www.TeachMsOffice.com for more, including Excel Consulting, Macros, and Tutorials. Learn how to install add-ins in this Excel video tutorial. You will learn how to install add-ins such as the Analysis Toolpak and Solver in Excel. This is a very good tutorial to learn if you just upgraded to Excel 2007 or just need to find out where to install add-ins in Excel. Installing the Analysis toolpak will allow you to perform regression analysis and t-stat and anova analysis in Excel in a much more efficient fashion than just using functions such as LINEST(). For Excel consulting, classes, or to get the spreadsheet or macro used here visit the website http://www.TeachExcel.com There, you can also get more free Excel video tutorials, macros, tips, and a forum for Excel. Have a great day!
Views: 86727 TeachExcel
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:39) Define Hypothesis Testing and example of questions we can ask. 2. (07:13) 5 Steps in Hypothesis Testing 3. (08:30) Review of variables we use in Hypothesis Testing 4. (8:30) Example of 1 tail test to the right (upper end) when sigma is known 5. (10:59) Step 1: Setting up Null and Alternative Hypothesis 6. (19:40) Step 2: Select Alpha, Level of Significance, Error of rejecting Null even though it is true. 7. (26:08) Step 3: Take Sample, Calculate Test Statistic, Draw Picture 8. (29:16) Test 4: Calculate Critical Value and/or P-value 9. (36:45) Make written concluding statement
Views: 12782 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:24) Percentile where position is calculated with P%*(n+1) 2. (04:26) PERCENTILE.EXC function 3. (05:18) What percentile tells you 4. (06:09) QUARTILE.EXC function 5. (06:59) PERCENTILERANK.EXC function 6. (07:45) Median 7. (08:48) Quartile 1, 2, 3 8. (09:34) Quintiles 9. (10:03) Deciles 10. (10:24) Percentile rank for whole data set 11. (11:24) PERCENTILE.INC, QUARTILE.INC functions 12. (14:13) Five Number Summary 13. (14:40) Big Data Set to see that PERCENTILE.INC and PERCENTILE.EXC are very close if not exactly the same. 14. (16:29) Ranking. RANK,EQ and RANK.AVE functions 15. (18:46) ) PERCENTILERANK.INC function 16. (19:00) Data Ribbon Tab, Analysis group, Data Analysis, Rank and Percentile feature
Views: 14064 ExcelIsFun
Download files: http://people.highline.edu/mgirvin/excelisfun.htm Topics in this video: 1. (00:14) Weighted Mean Quiz Score Example and Weighted Mean calculation using a Helper Column 2. (02:47) SUMPRODUCT and SUM function for single Cell Formula 3. (04:47) Calculate Weighted Average for Accounting COGS and End Inventory 4. (08:10)Calculate Weighted Average or “Expected Value” given a Percent Frequency Table
Views: 11536 ExcelIsFun