AI Doesn’t Know This: Best Solution for Excel Table Running Count Error! #Short Excel Trick 63

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ExcelShorst63.xlsx
Learn how to create a simple and easy running count formula in an Excel Table. ROWS Functions with special trick.

Learn #excel #excelformulas #excelfunctions #Short #excelisfun #microsoft #datamodeling #dataanalysis #office365 #office365training

BYROW, BYCOL, TOROW, TOCOL, FILTER, IFS & OR Eta Functions Do Lookup Magic. Re-do EMT 1426. EMT 1871

Download Excel File: https://excelisfun.net/files/EMT1870-1871.xlsx
Learn how to lookup a value in a rectangle range and return the row or column header with the best and easiest formula ever!!! I did a video 7 years ago (EMT 1426) and the formula was much harder. But now with MS 365 Excel: WOW!!! Everything is so much easier : ) Thanks to ExcelLambda from YouTube for the rad formulas!
Topics:
1. (00:00) Introduction.
2. (00:54) FILTER & BYCOL functions with the Eta Lambda OR Function to create lookup formula that returns column header (Sales Team / Sales Rep Lookup Table example).
3. (02:49) IFS & Continue reading “BYROW, BYCOL, TOROW, TOCOL, FILTER, IFS & OR Eta Functions Do Lookup Magic. Re-do EMT 1426. EMT 1871”

Excel’s WRAPCOLS Array Function to Undo Table Into a Specified # of Columns. #Short Excel Trick #62

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ExcelShort60-62.xlsx
Learn how to use the WRAPCOLS function to take a proper table and move the data into a specified number of columns (how many items encountered in column must be moved to each new column..

Learn #excel #excelformulas #excelfunctions #Short #excelisfun #microsoft #datamodeling #dataanalysis #office365 #office365training

GROUPBY & PERCENTOF Functions Not Work With Text Data!?!? Here Is Solution!! Excel Magic Trick 1870

Download Excel File: https://excelisfun.net/files/EMT1870-1871.xlsx
Learn how to use the GROUPBY function with the eta lambda function COUNTA and PERCENTOF to create frequency distribution that works with text data. Create a count column and a % of Column Total Column with the GROUPBY Function.
ExcelLambda from YouTube supplies awesome solution!
Topics:
1. (00:00) Introduction.
2. (00:21) PivotTables vs. GROUPBY or PIVOTBY Function
3. (00:57) First attempt at using GROUPBY Function. See functions GROUPBY, HSTACK, COUNT and PERCENTOF.
4. (02:00) Why PivotTable correctly calculates % of Column Total for Text Data
5. (02:29) Algorithm for PERCENTOF Function and how it is different than a PivotTable % of Continue reading “GROUPBY & PERCENTOF Functions Not Work With Text Data!?!? Here Is Solution!! Excel Magic Trick 1870”

No More VBA! Amazing WRAPROWS Function Converts Column of Records to Table. #Short Excel Trick 61

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ExcelShort60-62.xlsx
Learn how to convert a column of records to a proper table without VBA, using the Microsoft 365 Excel Array Function, WRAPROWS function. See Two Examples.

Learn #excel #excelformulas #excelfunctions #Short #array #dynamicspill #arrayformulas #office365 #office365training #data #datascience #database #datamodelling #tablestructure #table #peopertable #microsoft Excel #excelisfun #wraprows

Best Formula for % Change (Daily Stock Returns). #Short Excel Trick 60

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch11/ ExcelShorst60-62.xlsx
Learn how to create a dynamic spilled array formula to calculate the daily returned for three columns of stocks. Learn that the easiest formula for 5 change is End/Begin – 1 , not (End – Begin)/Begin.

Learn #excel #excelformulas #excelfunctions #Short #365excel #office365 #office365training #excelfinance #percentage #dailyreturns #excelisfun #change #changeyourlife

Stock Portfolio Expected Return & Beta with single cell array formula. Bonus Trick Too! #Short 59

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch10/ExcelShort57-59.xlsx
Learn how to create single cell array formula that allows you to skip many intermediate steps so you can calculate the expected return and beta for a portfolio of stocks using array calculations and the SUM function

Learn #excel #excelformulas #excelfunctions #Short #excelfinance #beta #returns #portfoliomanagement

Expected Return & Standard Deviation 4 Portfolio of Stocks Single Cell Excel Array Formula #Short 58

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch10/ExcelShort57-59.xlsx
Learn how to create single cell array formula that allows you to skip many intermediate steps so you can calculate the expected return and standard deviation for a portfolio of stocks using array calculations and the BYROW, SUM and SQRT functions.

Learn #excel #excelformulas #excelfunctions #Short
#excelfinance #standarddeviation #BYROW #expectedreturn #excelisfun

Expected Return & Standard Deviation for 2 Stocks: Single Cell Excel Spilled Array Formula #Short 57

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch10/ExcelShort57-59.xlsx
Learn how to create single cell array formula for stock A and stock B to calculates and spills the Expected Returns and Standard Deviations using array calculations and the BYCOL, SUM and SQRT functions. Also see that we can use BYCOL and BYROW lambda eta functions that are not from the dropdown list of functions. See that we can use the SQRT function inside BYCOL and BYROW functions

#excel #excelformulas #excelfunctions #Short #excelfinance #BYCOL #BYROW #arrays #expectedreturn #standarddeviation

Create Column of 8 Excel Text Formulas from Rectangle Range with Single Spilled Formula. #Short 56

Download Excel file: https://people.highline.edu/mgirvin/AllClasses/455/ch10/ExcelShort55-56.xlsx
Learn how to create text formula labels from a rectangualr range with elements that the formula needs from the column headers and row headers. Amazing Dynamic Spilled Array Formula. See the TEXT, FOMULATEXT and TOCOL functions as well as the Ampersand, the Join Operator.

Learn #excel #excelformulas #excelfunctions #Short #textformatting #ampersand #formulatext #excelisfun