Category: Excel

Excel Why Wont This SUMIFS Work Inside Of Let 2518

Posted on by Annie D

Stevenson asks: Why won’t this SUMIFS work inside of a LET function in Excel?
It turns out that he is trying to do a calculation in the first argument of the SUMIFS.
This is against the rules.
If you would have tried it without the LET, Excel would have given you the mysterious error of “There’s a Problem With This Formula”.
When you try to do it inside of LET, the formula parser is not smart enough to refuse the formula, but you end up with a bunch of #VALUE! errors in Excel.
The same limitation applies to SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS, SUMIF, AVERAGEIF, Continue reading “Excel Why Wont This SUMIFS Work Inside Of Let 2518” →

Is Excel DATEDIF Gone from Excel? #shorts

Posted on by Annie D

C. R. asks: Is DATEDIF gone from Excel? It is missing from the latest beta builds. Luckily, it is still there, but you have to know the arguments because Excel won’t provide any guidance.

This video answers these questions:
is datedif still in excel
is datedif in excel 2016
why does datedif not work in excel
does datedif still work
can’t find datedif in excel
does datedif still work
datedif how to use
how excel datedif works
how to add datedif function in excel
how to get datedif in excel
how to enable datedif function in excel
what replaced datedif in excel
how to use datedif

Logical Tests: Excel Formulas, Conditional Formatting, PivotTables, Power Query, & More- 365 MECS 07

Posted on by Annie D

Download Excel file: https://excelisfun.net/files/07-M365ExcelClass.xlsx
Download pdf notes: https://excelisfun.net/files/07-M365ExcelClass.pdf
Download csv file for Power Query example: https://excelisfun.net/files/9112021-2022CallData.csv
Course taught by Excel MVP and Highline College Professor, Mike Girvin. Course is Microsoft 365 Excel Complete Story.
Topics in video:
1. (00:00) Introduction
2. (01:00) What is a Logical Test in Excel?
3. (04:30) Examples of six most common types of logical tests with Filter Feature and PivotTable
4. (04:30) Examples of Single Condition, Contains and NOT Logical Tests
5. (08:44) Description of AND Logical Test and OR Logical Test
6. (17:16) Description of a Complex Logical Test
7. (20:09) Comparative Operators
8. (21:09) Comparative Continue reading “Logical Tests: Excel Formulas, Conditional Formatting, PivotTables, Power Query, & More- 365 MECS 07” →

Excel Habit Tracker Count Current Streak – 2517

Posted on by Annie D

How to calculate the current streak in a habit tracker.
Amreen is building a habit tracker in Excel. For each goal, he wants to report the current streak.
I show how to use XMATCH to solve this problem.

Table of Contents()
(0:00) The goal
(0:40) XMATCH formula for last 1
(1:08) Last zero
(1:29) No streak?
(1:45) How long is streak?
(2:30) Episode 735 for longest streak
(3:05) Converting to checkmarks
(4:32) Colors in Excel number format

Excel Statistical Analysis for Business – Busn 210 – Fall 2022 Quarter Introductory Video

Posted on by Annie D

Busn 210, Excel Statistics for Business class taught by Excel MVP Mike Girvin at Highline College.
This is the intro video for matriculating students at Highline College in fall quarter, 2022.
To watch at YouTube here is the playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0m3oqpp1XcPuaxyM4Bpi0dN

Busn 218 – Advanced Excel – Fall 2022 Quarter Introductory Video

Posted on by Annie D

Busn 218, Advanced Excel class taught by Excel MVP Mike Girvin at Highline College.
This is the intro video for matriculating students at Highline College in fall quarter, 2022.
To watch at YouTube here is the playlist:
https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW

Excel VBA User Defined Function To Generate A Dynamic Array – 2516

Posted on by Annie D

Can Excel VBA return a dynamic array from a user defined function to the grid?
Can you repeat each cell in column A based on the number in column B?

Check out the amazing answers from Smozgur and XLLambda here:
https://www.mrexcel.com/board/threads/repeatbynumber.1216836/

Table of Contents
(0:00) Two titles
(1:11) Using VBA
(2:05) How many cells to return
(2:50) Building the array
(3:22) Return array to grid
(4:00) Using Hash array operator

Excel Insert Blank Row After Every Two Data Rows #Shorts

Posted on by Annie D

LC has 4000 rows of Excel data. After every two rows, he wants to insert a blank row. How can he quickly insert 2000 blank rows in the data? My solution today involves a formula to create alternating cells with text and numbers. Then Home, Find & Select, Go To Special. Select all Formula cells that result in Text. This selects every other row. You can then Insert Sheet Rows and you are done in less than 60 seconds.

Excel FILTER To 2 conditions with LEFT #Shorts

Posted on by Annie D

Keith wants to use the Excel FILTER function to get all names where two conditions are true: Status is A and the Project does not start with 22-05. This video shows how to combine 2 conditions in an Excel FILTER formula.

excel filter function
why excel filter not working
excel can you filter columns
excel can you filter with LEFT
excel can you filter to two conditions

Excel Dynamic Charts Based On Dynamic Arrays – 2515

Posted on by Annie D

Brand new in Excel Insiders Beta:
A chart can be based on an entire dynamic array in Excel.
In this first iteration, the chart has to point to the entire array. If you have columns in your array that you don’t want to chart, you should use =CHOOSECOLS to get a smaller subset of the array for charting.

Text Formulas & Functions in Excel Worksheet and Power Query – 365 MECS 06

Posted on by Annie D

Download Excel file: https://excelisfun.net/files/06-M365ExcelClass.xlsx
Download pdf notes: https://excelisfun.net/files/06-M365ExcelClass.pdf
Course taught by Excel MVP and Highline College Professor, Mike Girvin. Course is Microsoft 365 Excel Complete Story.
Topics in video:
1. (00:00) Introduction
2. (00:55) Join three items with the Join Operator, Ampersand &
3. (01:57) Why you use formulas rather than Flash Fill
4. (02:18) TEXTJOIN function to join three items with
5. (03:02) CONCAT function to join three items with
6. (03:16) Extract data from cell with dash delimiter to use in lookup formula. Learn about TEXTBEFORE, TEXTAFTER and XLOOKUP functions.
7. (04:43) XLOOKUP Error: Data Mismatch and how to fix Continue reading “Text Formulas & Functions in Excel Worksheet and Power Query – 365 MECS 06” →

Excel IF Contains – 2514

Posted on by Annie D

Excel IF Contains
Excel IF cell contains text
Excel IF contains
excel like wildcard
excel wildcard
excel like wildcard if

How can you test which cells in Excel contain text? Can you use an IF statement in Excel to find which cells contain certain text?

This video looks at COUNTIF, FIND, SEARCH, LOWER, SIGN, ISNUMBER, IFERROR functions.

Table of Contents
(0:00) Question
(0:22) Check for equality
(0:41) Wildcards fail in IF
(1:05) Using COUNTIF with wildcard
(1:39) Using FIND in Excel
(2:23) Adding SIGN, IFERROR, LOWER
(2:59) Using SEARCH in Excel
(3:25) Using ISNUMBER with SEARCH
(4:22) Excel IF contains solution
(4:44) Finding Contains using Filter
(5:11) Nancy Faust