Category: Excel

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” →

Excel Statistical Analysis 39: Confidence Intervals: Theory for Estimating Population Parameter

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 theory for why and how we use Confidence Intervals to estimate Population Parameters
Topics:
1. (00:00) Introduction
2. (00:22) Compare what we did in Chapter 7 (Compare Sample Mean to Sampling Distribution Of Sample Means or Standard Normal Curve) and Chapter 8 (Build Confidence Interval to estimate Reasonable Range for Population Parameter)
3. (05:47) Pictorial Example of risk associated with estimating Population Mean with a Confidence Interval
4. (07:19) Examples when Population Mean cannot be calculated
5. (08:09) Define Confidence Intervals
6. (12:01) Excel Worksheet Functions to create Confidence Intervals
7. (13:53) Continue reading “Excel Statistical Analysis 39: Confidence Intervals: Theory for Estimating Population Parameter” →

Excel Statistical Analysis 38: Normal Bell Curve to Estimate Range of Sample Proportions

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/Ch07-ESA.xlsm PDF notes file: https://excelisfun.net/files/Ch07-ESA.pdf
Learn about using the Normal Bell Curve to estimate a reasonable range of Sample Proportions.
Topics:
1. (00:00) Introduction
2. (00:21) Formulas for using Normal Bell Curve to estimate a reasonable range of Sample Proportion Values.
3. (02:42) Proportion of Americans who balance checkbook example.
4. (07:08) Proportion of insurance policies that fall within a two standard deviation margin of error
5. (09:56) Summary of video
6. (10:09) Closing, Next Video and Video Links

VLOOKUP Into “Vendor – URL” With TEXTBEFORE – 2486

Posted on by Annie D

Today’s Excel question from Willie at the UCF Accounting Conference:
I am using a vlookup to get some information from a vendor info database that comes from the credit card website.
 
My issue is that the vendor name sometimes comes with additional info at the end like …. “Contoso – cadet.biz” and in the table where I do the lookup the name is just “Contoso”.
 
I was wondering if you think I can use the “TEXTBEFORE” function with the Vlookup formula to make the formula to look in the table only for the first word of the info coming from the credit Continue reading “VLOOKUP Into “Vendor – URL” With TEXTBEFORE – 2486″ →