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
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 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
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
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.
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
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.
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
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
Aug 1, 2022 and the physical copy of excelisfun’s new book “The Only App That Matters” is out!
Publisher Bill Mr Excel Jelen $28.88 offer at this link between Aug 4 and Aug 8 with promo code promo code “OCHO3”: https://excel.bookstore.ipgbook.com/microsoft-365-excel–the-only-app-that-matters-products-9781615470709.php
Buy Book at Amazon (available more quickly and in more locations) for 34.99 (hard copy) or $18 for Kindle: https://www.amazon.com/Microsoft-365-Excel-Calculations-Analytics/dp/1615470700
excel formula not calculating
why excel formula is not calculating
when excel shows formula not result
when excel doesn’t calculate a formula
When excel shows the formula instead of the result
Table of Contents
(0:00) Excel shows formula not result
(0:04) Cell formatted as Text
(0:23) in Show Formulas mode
Four answers today:
Excel remove duplicates without shifting
Keep Duplicates Remove Everything Else
Which Row does Excel Keep during Remove Duplicates
Excel Get Duplicates Back
Formula to Remove Duplicates in Excel using UNIQUE and SORT functions
FILTER to keep only certain columns
Ctrl+z or Alt+Backspace to Undo
Topics discussed in this video include:
excel remove duplicates without shifting
excel only include duplicates
excel keep duplicates remove unique
excel keep duplicates remove everything else
excel keep duplicates
excel duplicates keep
how to get duplicates back in excel
can you add duplicates back in excel
excel list duplicates between two columns
excel remove duplicates using formula
excel duplicates remove formula
excel duplicates formula
excel is duplicate formula
in excel duplicate formula
excel no duplicates formula
excel Continue reading “Excel KEEP Duplicates Delete Everything Else – 2513”