Matrix Formulas and Functions

U1: Introduction

• Matrix Formulas
• Dynamic Array Formulas vs. Legacy CSE Array Formulas
• Matrix spilled, or, overflowing.
• Precautions and limitations.
• Example of spill or overflow.
• Overflow error.
• Implicit operator @.
• Spilled range operator (#).
• Use of the dollar symbol.
• the SORT matrix function.
• The ORDERBY matrix function.
• The UNIQUE function.
• The TRANSPOSE function to reverse rows by columns.
• The SEQUENCE matrix function.
• The FILTER matrix function.
• Names and days of the week (FILTER).
• Report tied matches (FILTER).
• The FREQUENCY function.
• find repeated values ​​(FREQUENCY).
• The matrix function MATRIZALEAT.
• The MMULT function.
• The TEXTBEFORE, TEXTAFTER, and SPLITTEXT functions.
• VSTACK and HSTACK functions.
• Consecutive repeated birds (hstack/apilarh).
• The TOCOL (ENCOL) and TOROW (ENROW) functions.
• Countries that improved and those that worsened (ENCOL).
• Extract text between quotes (enqueue).
• Invert every 2 rows and 3 rows (ENCOL).
• Fruit counting (ENCOL).
• Split sales in table2 (ENCOL).
• The TAKE and DROP functions.
• Column model (EXCLUDE).
• The WRAPCOLS and WRAPROWS functions.
• The WRAPCOLS and WRAPROWS functions.
• The CHOOSEROWS and CHOOSECOLS functions.
• Fruits between dates (ENCOL).
• The EXPAND function.
• WRAPROWS function error.
• GROUPBY, PIVOTBY, PERCENTOF.

U2: Example of functions and matrix formulas

• UNIQUE function. Counting duplicate values.
• Example of functions with or without matrix functions.
• Table with months and years created in a matrix format and with graphs and mini graphs
• Dynamic graph from spilled matrix.
• SUMIF, INDEX, TRANSPOSE with spilled matrices.
• FREQUENCY and INDEX.
• Create a pivot table from pivot arrays.
• Calculation of maximum and minimum values ​​per year.
• Create custom formula with spilled arrays.
• Create a calendar.
• Create drop-down lists with spilled range operator.
• Create a drop-down list with auto-fill.
• Create an updatable annual and monthly calendar.
• Drop-down list with manual auto-fill.
• Total by product and header selected with the SUM function.
• Example of the FILTER and IF.SET functions.
• Example of the FILTER and CONCATENATE functions.
• Examples of the SEQUENCE, VLOOKUP, COUNTA, and MATCH functions.
• How to calculate the total for the last month of each quarter.
• Example of the FILTER, TEXTBEFORE, and TEXTAFTER functions.
• Second part example FITRAR, TEXTBEFORE, TEXTAFTER.
• Example of the FILTER, and XLOOKUP functions.
• Search by first name, last name 1, and last name 2.
• Find the first sale of each month for each year.
• Example of the INDEX, SEQUENCE, and AGGREGATE functions.
• Example of the FILTER, TEXT, HSTACK, UNIQUE functions.
• Example of cut out people.
• Put borders on overflowed matrix.
• Extract duplicate records.
• Compare dates from the same month in two years up to the last day of sales in the first year.
• Statistical table of the year.
• Calculate totals by province, on different sheets.
• Counting of provinces by centers and products.
• Obtain unique provinces while the province column is in another position.
• Place the products in their column.
• Find month(s) with the most sales.
• Retrieve headers from selected tables.
• Create a copy of a table by replacing the IDs with their names.
• create a drop-down list for three table headers.
• Filter by province, center, product, and year.
• Filter by first and second header dynamically.
• Example of the FILTER, and, EXPAND functions.
• Select a year, and the years following the selected one will appear.
• Calculation of total less discount.
• Calculate shipping cost.
• Calculate sales and sales shipped.
• Find the employees for each day of the month.
• split a table into different sheets.
• Distinguish between uppercase and lowercase letters.
• filter by four criteria.
• Calculation of total by product, and the five highest totals.
• Calculate total by selected header.
• Calculate veins for a given day and time.
• Discount calculation based on quantity and product.
• Calculation of total by province, and, table.
• Report by league seasons.
• Commissions per seller and product, and total per seller.
• Total by date and province.
• Products sold by month.
• Counting quantities.
• Total by province, center, and year.
• Search price by shopping center.
• Correct name.
• Create matrix in rows.
• Quantities between one and two provinces.
• Sum of total excluding empty cells.
• Find price closest to date.
• Report dates according to product.
• Transpose data model.
• Calculation of the total by item and color.
• most and least sold product.
• Total by province.
• Find provinces that are not found.
• Products by province.
• Split model into columns.
• Convert rows to columns.
• Split center, product and date into rows.
• Report by centers and equal products.
• Find center for each product.
• Months to finish a task.
• Products sold after 90 days.
• Search by the initials of a province.
• Count province/center in a single function.
• Total center/product and total by product.
• Quantity by province, product and date.
• Transfer model in row to columns.
• Find price of selected product and packaging.
• Filter model without empty columns and rows.
• Find the days of no sales.
• Find the days of no sales over several years.
• Search center by initials.
• Insert blank line between provinces.
• Total by provinces.
• Report without weekends.
• Number of times each province appears and total per province.
• Remove rows equal to Granada and Huelva.
• Extract product and price.
• Graph from January to selected month.
• Replace more than one word.
• Join 2 tables without empty column.
• Calendar with dates with and without sales.
• Delete empty rows and columns.
• Reports by province, center, and product.
• Comparison report of years and quarters.
• Replace one sentence with another.
• Assign workers to weekdays.
• Create duplicate report and sales hours by dates.
• Convert number to years and months.
• Report by minimum and maximum value.
• ID by dates.
• rear standard model
• Extract word by index

Course evaluation

• This course contains a final exam

G-Tools: For Students

• Exclusive access to cutting-edge student tools: improve your employability, participate in exclusive events, take advantage of our intelligent virtual assistant, and more.

⚑ Certificate of completion

• Your personalized digital certificate, a unique badge of your achievements, with international validity, course duration and QR code for instant verification.