Antony in Toronto brings us two more Excel AutoSum Oddities.
This was part of my hour-long session on Excel Oddities, Curiousities and Mysterious Wonders for the Vancouver Modern Excel Users Group. Watch the entire hour here:
https://youtu.be/H7rSetB7IRo?t=277
Doug from Boston needs to calculate an age in Excel.
Two easy formulas today: YEARFRAC and DATEDIF.
Download Excel File: https://excelisfun.net/files/EMT1776.xlsx
Learn how to build a simple formula to automatically extract invoices that are 1 to 30 days past .
Topics:
1. (00:00) Introduction
2. (00:27) History about how to do it in Any Version of Excel.
3. (02:02) Continue reading “Extract Records For Invoices 1 to 30 Days Past Due – Dynamic Excel Formula Method – EMT 1776”
Today’s question from Ken: I’ve lost a slicer. I know it is somewhere on the worksheet, but how can I find it?
Thanks to Lou Pham, we have an answer. Use Home, Find & Select, Selection Pane. Select the object. Press Continue reading “F2 To Bring Selected Object Back Into View in Excel – 2471”
Download Excel File: https://excelisfun.net/files/EMT1775.xlsx
Learn 3 formula methods to add the top three values.
Learn how to.
1. (00:00) Introduction
2. (00:12) Look at the issues that make this problem difficult
3. (00:59) 1st solution: SUM, LARGE and SEQUENCE solution
4. (01:10) 1st Continue reading “Sum Top 3 Values When There Is Missing Data – 3 Unbelievable Solutions (LAMBDA too) EMT 1775”
Gagan has sales for three years. For each month in those three years, what is the first date that had a sale? For some reason, Gagan needs to do this without a helper column.
My first solution is a pivot table Continue reading “Excel – Find The Date Of The First Sale In Each Month – 2470”
Download Excel File: https://excelisfun.net/files/Ch04-ESA.xlsm
Learn about 6 different methods for calculating probabilities using Bayes Theorem.
Topics:
1. (00:00) Introduction
2. (00:16) Introduction to Baye’s Theorem with a CPA Test Score Example
3. (03:17) Probabilities from Table of Data using Worksheet Formulas and Continue reading “Excel Statistical Analysis 22: Bayes’ Theorem, Tabular Method, Probability Tree, SUMPRODUCT function”
Download Excel File: https://excelisfun.net/files/Ch04-ESA.xlsm
Learn about the Multiplication Law of Probability to calculate AND Logical Test Probabilities. Learn how to build a probability tree too.
Topics:
1. (00:00) Introduction
2. (00:17) Look back at Conditional Formatting Probability Rule and how it Continue reading “Excel Statistical Analysis 21: Multiplication Law of Probability, AND Logical Test, Probability Tree”
Download Excel File: https://excelisfun.net/files/ETM1774.xlsm
Learn how stop references to PivotTable as showing up with GETPIVOTDATA Functions. Learn how to create formulas with cell references that refer to PivotTable.
Download Excel File: https://excelisfun.net/files/Ch04-ESA.xlsm
Learn what a Joint Probability Table is and how to create them with a PivotTable and Dynamic Spilled Array Formulas.
Topics:
1. (00:00) Introduction
2. (00:29) What is a Joint Probability Table
3. (01:58) Create Joint Probability Table Continue reading “Excel Statistical Analysis 20: Joint Probability Table: Dynamic Spilled Array or PivotTable?”
Download Excel File: https://excelisfun.net/files/ETM1774.xlsm
Learn how stop references to PivotTable as showing up with GETPIVOTDATA Functions. Learn how to create formulas with cell references that refer to PivotTable.
Download Excel File: https://excelisfun.net/files/Ch04-ESA.xlsm
Learn about:
Topics:
1. (00:00) Introduction
2. (00:40) Define Conditional Probability
3. (02:58) Calculate Conditional Probability From a Cross Tabulated Frequency Table using worksheet formulas
4. (05:38) Two ways to phrase a Conditional Probability statement.
5. (05:54) Key to understanding Continue reading “Excel Statistical Analysis 19: Conditional Probability 5 Examples”