Category: Excel

Excel Statistical Analysis 30: Introduction to Continuous Probability Distributions

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch06-ESA.xlsm PDF notes file: https://excelisfun.net/files/Ch06-ESA.pdf
Learn about Continuous Probability Distributions.
Topics:
1. (00:00) Introduction
2. (00:20) Compare Discrete Probability Distributions to Continuous Probability Distributions
3. (05:05) Continuous Probability Distributions Density Function to calculate the height of plotted curve of continuous probability distributions
4. (05:56) Integral Calculus or Excel functions to calculate the area under the curve, or probability
5. (07:03) Three examples of Normal, Uniform and Exponential Distributions.
6. (08:26) The distributions we learn about in this chapter use population data, not sample data.
7. (08:57) Next Video, Closing and Video Links

XLOOKUP Function, FILTER Function, VLOOKUP function and LOOKUP Function: Amazing Tricks! EMT 1791

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1791.xlsx
Learn three important lookup formula tricks: Lookup Last Item, Lookup First Item, Lookup and Return All Matching Records.
Topics:
1. (00:00) Introduction
2. (00:13) Product Lookup Table
3. (00:46) Lookup First using XLOOKUP function (Microsoft 365 = New School)
4. (01:38) Lookup Last using XLOOKUP function (Microsoft 365 = New School)
5. (02:29) Lookup All Matching Records using FILTER function (Microsoft 365 = New School)
6. (03:32) Lookup First using VLOOKUP function (Any Version of Excel)
7. (04:18) Lookup Last using LOOKUP function and Array Operation (Any Version of Excel)
8. (06:11) Lookup All Matching Records using Complicated Array Formula (Any Version Continue reading “XLOOKUP Function, FILTER Function, VLOOKUP function and LOOKUP Function: Amazing Tricks! EMT 1791” →

Excel Can You Merge Cells In A Table – 2483

Posted on by Annie D

Uncle S is trying to have a formula copy to new rows. So a table seems like a great way. But he also has merged cells and the table unmerges those.

In this video, two surprising Table tricks:
First, a table can contain Center Across Selection, which is better than Merging Cells in Excel.
Second, a surprising discovery from the Excelforo channel about new headings in a table.

Table of Contents
(0:00) Auto-copying formulas to new rows in Excel
(2:31) Can a table have a merged cell
(4:00) New table columns using custom lists in Excel

Excel Statistical Analysis 29: HYPGEOM.DIST Function for Conditional Probabilities Across # Trials

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch05-ESA.xlsm PDF notes file: https://excelisfun.net/files/Ch05-ESA.pdf
Learn about Hypergeometric Distribution and HYPGEOM.DIST Function to calculate AND Logical Test probabilities for when you have Conditional Probabilities Across a Fixed Number of Trials.
Topics:
1. (00:00) Introduction
2. (00:30) AND Logical Test with Conditional Probabilities for Pulling Two Straight Queens in Two Tries. Long Hand Formula
3. (04:55) HYPGEOM.DIST Function to calculate AND Logical Test probabilities for when you have Conditional Probabilities Across a Fixed Number of Trials. For Hypergeometric Distribution
4. (06:35) Pulling 2 Queen Cards in Five Tries Probability from sample points and with HYPGEOM.DIST function
5. (09:01) HYPGEOM.DIST Continue reading “Excel Statistical Analysis 29: HYPGEOM.DIST Function for Conditional Probabilities Across # Trials” →

Excel Pivot Table Months Or Weekdays Are Alphabetical – 2482

Posted on by Annie D

You have a pivot table in Excel. The months and weekday names are appearing alphabetically instead of in calendar sequence. This happens when your pivot table is based on external data or the data model.
There are two solutions. This video prefers using the Custom Sort Order option. This takes 8 clicks to fix the months and 8 clicks to fix weekdays.
If your Pivot Table Slicers are in the wrong sequence, then you should use the Data Model setting to sort one column by another column.

Excel Formulas, Text To Columns or Power Query to Fix Dates. Excel Magic Trick 1790

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1790.xlsx
Learn how to convert improper dates (text or YYYMMDD dates) into proper serial number dates.
Topics:
1. (00:00) Introduction
2. (00:19) Worksheet Formula Method, TEXT function
3. (01:18) Text To Columns Method
4. (02:34) Power Query Method
5. (03:41) Summary, Closing, Video Links

Excel Statistical Analysis 28: Poisson Distribution, POISSON.DIST function and Poisson Column Chart

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch05-ESA.xlsm PDF notes file: https://excelisfun.net/files/Ch05-ESA.pdf
Learn about the Poisson Distribution and how to calculate probabilities using the POISSON.DIST function.
Topics:
1. (00:00) Introduction
2. (00:22) Data for arrivals at Dick’s Hamburgers restaurant
3. (01:05) Poisson Distribution Characteristics and requirements
4. (03:36) Poisson frequency Distribution pattern
5. (04:35) POISSON.DIST function
6. (08:22) Plot a Poisson Distribution on an Excel Column Chart
7. (09:31) Examples of when you can use Poisson Distribution, kike Waiting and stretches of road
8. (11:02) Poisson Math Formula
9. (11:20) The number e
10. (11:40) EXP worksheet function to calculate the number e
11. (12:20) POISSON.DIST function for the Dick’s Hamburgers Continue reading “Excel Statistical Analysis 28: Poisson Distribution, POISSON.DIST function and Poisson Column Chart” →

Find Next True And The Matching Records Above And Below – 2480

Posted on by Annie D

Michele has 46000 rows of data. Each ID has several records in a row. Many records are marked as “Changed”. When there is a change, Michele needs to check the record marked, plus the matching IDs just above and below.

In this video, I try Find All and 2 Advanced Filters. I bet you have something better.

Excel Statistical Analysis 27: Binomial Probability Distributions. BINOM.DIST.RANGE Function & Chart

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch05-ESA.xlsm PDF notes file: https://excelisfun.net/files/Ch05-ESA.pdf
Learn about:
Topics:
1. (00:00) Introduction
2. (00:29) First look at binomial probability math formula.
3. (01:12) Requirements for a Binomial Experiment.
4. (04:46) Why the Binomial Probability Math Formula works. Look at Samples Points and AND Logical Test and the OR Logical Test. Learn about the reason the math formula is valid.
5. (10:06) BINOM.DIST and ) BINOM.DIST.RANGE Excel Built-in Functions.
6. (11:08) Meand and Standard Deviation formulas for a Binomial Experiment.
7. (11:38) Example with Excel formulas to calculate a Binomial Probability with 1) Sample Point Method, 2) Math Formula Method, 3) BINOM.DIST.RANGE Continue reading “Excel Statistical Analysis 27: Binomial Probability Distributions. BINOM.DIST.RANGE Function & Chart” →

Excel Statistical Analysis 26: Accounting & Finance Discrete Probability Distribution Examples

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch05-ESA.xlsm
See two examples of how to use a Discrete Probability Distribution Accounting & Finance. See a cash flow example in Accounting and a Stock Return Example in Finance.
Topics:
1. (00:00) Introduction
2. (00:22) Accounting Example using Expected Value and Standard Deviation to estimate cash flows for a sales situation.
3. (03:51) Finance Example using Expected Value and Standard Deviation to estimate stock returns and risk of the stock.
4. (09:04) Summary of video
5. (09:17) Closing and Video Links