Category: Excel

Excel Statistical Analysis 49: Hypothesis Testing for Proportion (Binominal) using Normal Curve

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch09-ESA.xlsm Download 2 PDF note files: https://excelisfun.net/files/Ch09-ESA.pdf, Ch09-ESA-JustFormulas.pdf
Learn about how to use the NORM.S.DIST and NORM.S.INV Excel functions to run a hypothesis test for a proportion.
Topics:
1. (00:00) Introduction
2. (00:21) Hypothesis Testing for Proportion (Binominal) using Normal Curve. Student knowledge of presidential election example. See NORM.S.DIST and NORM.S.INV Excel functions.
3. (07:11) Summary of video, Closing, Next Video and Video Links

Excel Statistical Analysis 48: Hypothesis Testing with T Distribution, Two Tail Example

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch09-ESA.xlsm Download 2 PDF note files: https://excelisfun.net/files/Ch09-ESA.pdf, Ch09-ESA-JustFormulas.pdf
Learn about Two Tail Hypothesis Test with t Distribution. Learn about the Excel worksheet functions T.DIST, T.DIST.2T, T.INV, T.INV.2T
Topics:
1. (00:00) Introduction
2. (00:21) Two Tail Test with t Distribution from point of view of Seattle Used Auto Dealers
3. (02:30) Formulas for calculations from sample data
4. (04:04) Calculate p-value using the T.DIST function or T.DIST.2T function
5. (06:22) Calculate critical value using the T.DIST function or T.INV.2T function
6. (08:40) Summary of video, Closing, Next Video and Video Links

Excel Statistical Analysis 47: Hypothesis Testing with T Distribution, 1 Tail Lower (Left) Test

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch09-ESA.xlsm Download 2 PDF note files: https://excelisfun.net/files/Ch09-ESA.pdf, Ch09-ESA-JustFormulas.pdf
Learn about 1 Tail Lower (Left) Hypothesis Test with t Distribution. Learn about the Excel worksheet functions T.DIST and T.INV
Topics:
1. (00:00) Introduction
2. (00:24) 1 Tail Lower (Left) Test with t Distribution from point of view of a Construction company that wants to decide if a new building technique is faster than an older technique
3. (02:46) Formulas for calculations from sample data
4. (04:20) Calculate p-value using the T.DIST function
5. (05:04) Calculate critical value using the T.INV function
6. (07:28 Closing, Next Video and Continue reading “Excel Statistical Analysis 47: Hypothesis Testing with T Distribution, 1 Tail Lower (Left) Test” →

Excel Statistical Analysis 46: Hypothesis Testing with T Distribution, 1 Tail Upper (Right) Test

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch09-ESA.xlsm Download 2 PDF note files: https://excelisfun.net/files/Ch09-ESA.pdf, Ch09-ESA-JustFormulas.pdf
Learn about 1 Tail Upper (Right) Hypothesis Test with t Distribution. Learn about the Excel worksheet functions T.DIST and T.INV
Topics:
1. (00:00) Introduction
2. (00:27) 1 Tail Upper (Right) Test with t Distribution from point of view of a fuse manufacturer who wants to test if a manufacturer machine is faster than an older machine
3. (04:55) Formulas for calculations from sample data
4. (06:56) Calculate p-value using the T.DIST function
5. (08:37) Calculate critical value using the T.INV function
6. (10:38) Closing, Next Video and Video Continue reading “Excel Statistical Analysis 46: Hypothesis Testing with T Distribution, 1 Tail Upper (Right) Test” →

Excel Statistical Analysis 44: Hypothesis Testing with Z Distribution, 1 Tail Lower (Left) Test

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch09-ESA.xlsm Download 2 PDF note files: https://excelisfun.net/files/Ch09-ESA.pdf, Ch09-ESA-JustFormulas.pdf
Learn about One Tail Hypothesis Test to the Left with z Distribution. Learn about the Excel worksheet functions NORM.S.DIST and NORM.S.INV.
Topics:
1. (00:00) Introduction
2. (00:26) One Tail Test to the Left (Lower Test) with z Distribution from point of view of consumer group concerned with product consistency
3. (07:17) Calculate p-value using the NORM.S.DIST function
4. (08:50) Calculate critical value using the NORM.S.INV function
5. (10:36) Summary of video
6. (19:46) Closing, Next Video and Video Links

Excel Statistical Analysis 45: Hypothesis Testing with Z Distribution, Two Tail Test Example

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch09-ESA.xlsm Download 2 PDF note files: https://excelisfun.net/files/Ch09-ESA.pdf, Ch09-ESA-JustFormulas.pdf
Learn about Two Tail Hypothesis Test with z Distribution. Learn about the Excel worksheet functions NORM.S.DIST and NORM.S.INV

Topics:
1. (00:00) Introduction
2. (00:22) Two Tail Test with z Distribution from point of view of a manufacturer concerned with filling machine accuracy
3. (05:17) Calculate p-value using the NORM.S.DIST function
4. (07:39) Calculate critical value using the NORM.S.INV function
5. (09:31) Summary of video
6. (09:43) Closing, Next Video and Video Links

Excel Statistical Analysis 43: Hypothesis Testing: P-value & Critical Value Methods: 1 Tail Upper

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch09-ESA.xlsm Download 2 PDF note files: https://excelisfun.net/files/Ch09-ESA.pdf, Ch09-ESA-JustFormulas.pdf
Learn about the 5 steps in hypothesis testing. Learn how to run a hypothesis test in Excel.
Topics:
1. (00:00) Introduction
2. (00:39) What is a Hypothesis and what is Hypothesis testing?
3. (07:07) 5 steps in Hypothesis Testing
4. (08:23) Variables for Hypothesis Testing
5. (10:40) One Tail Upper (Right) Example for Mean Salary of Realtors
6. (11:22) Step 1: Null and Alternative Hypothesis
7. (17:58) Step 2: Determine Alpha, Type 1 Error Risk. What is Type 1, Alpha, Risk?
8. (23:00) Step 3: Collect Data, Compute Test Statistic
9. Continue reading “Excel Statistical Analysis 43: Hypothesis Testing: P-value & Critical Value Methods: 1 Tail Upper” →

Excel XLOOKUP Return Non Adjacent Columns – 2488

Posted on by Annie D

Another question from the UCF Accounting Conference. JoAnne asks… XLOOKUP can return multiple columns. What if you want to return several non-adjacent columns, for example, January, April, July, October?
There are three solutions presented here.
Table of Contents
(0:00) XLOOKUP return non-adjacent columns
(0:30) XLOOKUP with FILTER
(2:39) Using HSTACK
(3:17) VLOOKUP with Ctrl+Shift+Enter
(4:40) How would you solve it?

Excel Statistical Analysis 42: Confidence Interval for Proportions (Binomial Experiments)

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch08-ESA.xlsm PDF notes file: https://excelisfun.net/files/Ch08-ESA.pdf
Learn about how to build Confidence Interval for Proportions using Excel Formulas.
Topics:
1. (00:00) Introduction
2. (00:20) Confidence Internals for Proportions theory and formulas
3. (01:18) Employee Survey Example
4. (07:20) Furniture Company New Product example
5. (10:05) Summary of video
6. (10:14) Closing, Next Video and Video Links

Excel Statistical Analysis 41: Confidence Interval for t Distribution, use when Sigma NOT Known

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch08-ESA.xlsm PDF notes file: https://excelisfun.net/files/Ch08-ESA.pdf
Learn about how to create Confidence Interval to estimate a population Mean when Sigma (Population Standard Deviation) is NOT Known using the t Distribution and the Excel worksheet functions: T.INV, CONFIDENCE.T and also the Data Analysis tool.
Topics:
1. (00:00) Introduction
2. (00:27) t Distributions: Characteristics
3. (04:20) t Distribution Confidence Interval Formula
4. (04:48) Excel T Functions
5. (05:26) How t Curve changes as the Sample Size, n, changes
6. (06:34) Printer Cartridge Example With formulas to calculate sample statistics
7. (08:55) Method #1: T.INV function
8. (09:54) Method #2: CONFIDENCE.T function
9. (11:15) Method Continue reading “Excel Statistical Analysis 41: Confidence Interval for t Distribution, use when Sigma NOT Known” →

Sorted Summary Report WIth Totals In Excel Without A Pivot Table – 2487

Posted on by Annie D

A question from Elijah at the UCF Accounting Conference. I was showing how to use the UNIQUE function to get a list of products. Elijah asked if I could then get the total sales for each product. Plus a heading row and a total row. With the items sorted high to low.
The final result uses several new Excel functions: UNIQUE, SUMIFS, HSTACK, SORT, VSTACK, and LET.
This lets you create something that is like a pivot table, but there is no need to refresh as the data changes.
Table of Contents
(0:00) Welcome
(0:16) Formulas to Values
(1:08) UNIQUE list of products
(1:35) SUMIFS by Continue reading “Sorted Summary Report WIth Totals In Excel Without A Pivot Table – 2487” →

Excel Statistical Analysis 40: Confidence Interval for z Distribution, use when Sigma Known

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch08-ESA.xlsm PDF notes file: https://excelisfun.net/files/Ch08-ESA.pdf
Learn about how to create Confidence Interval to estimate a population Mean when Sigma (Population Standard Deviation) is known using the z Distribution and the Excel worksheet functions: NORM.INV, NORM.S.INV and NORM.CONFIDENCE.
Topics:
1. (00:00) Introduction
2. (00:22) Build Confidence Interval when Sigma is Known using the z Distribution
3. (02:59) Method #1 using: NORM.INV function
4. (04:15) Method #2 using: NORM.S.INV function
5. (05:27) Method #3 using: NORM.CONFIDENCE function
6. (06:20) Reasonable Statements that can be made when construction Confidence Intevals
7. (07:37) Be careful because Confidence Intervals do not always contain population Continue reading “Excel Statistical Analysis 40: Confidence Interval for z Distribution, use when Sigma Known” →