MrExcel Excel Videos

Long-time viewer Dave writes in with a potential bug in Excel. Why does RANDARRAY with a RANDBETWEEN inside evaluate to a #SPILL! error about 10 percent of the time? There is nothing in the way that should be causing the spill error. The sad news is that this is against the rules. Here is the support article: #SPILL! - Volatile Size The stranger question: why does it work 90% of the time?

The new Dynamic Array formulas in Excel are awesome. But it is very common to run into a known limitation. It is called an array of arrays and it is happening a lot to people who are trying to learn dynamic arrays.

A recent news story reported that 02/02/2020 was the first time in 900 years that we had a palindrome as a date. While the news story left out a bunch of additional details, I was wondering if you could easily fact-check that in Excel. In today's episode a way to use Dynamic Arrays, SEQUENCE, MID, and CONCAT to reverse the text in a cell. Also: Check out Fast Excel Speed Tools at FastExcel V4 SpeedTools for a REVERSE.TEXT function. Check out my Dynamic Arrays book: Guide to Excel Dynamic Arrays by MrExcel Table of Contents (0:00) Introduction (0:20) Clark Kent of Dynamic Array Functions: SEQUENCE (0:33) Broadcast News Rant (1:08) Formula to test for a Palindrome (2:00) Why the Excel formula works, step by step (2:25) TEXTJOIN versus...

To buy or try WBMerge: https://mrx.cl/2HVj0wU Save 25% on WBMerge through March 21 2020 using coupon code MREXCEL You have an Excel workbook and would like to split it by (customer or product or whatever) with each unique value going to a new worksheet or to a new workbook. The people who made WBMerge have added a WBSplit feature.

John has figured out how to use SUMIF to find all entries less than 10. But his next step is to find everything between 10 & 20. He asks how can he make SUMIF handle two conditions? In this video: How to switch from SUMIF to SUMIFS How to prevent having to edit each formula How to use a Pivot Table to do the analysis quickly And old-school SUMPRODUCT formula.

We've been using dynamic arrays in Excel for a while and there are some things that would make them a bit more useful! Today, a look at a new add-in from Excel MVP Charles Williams that allows you to add totals to dynamic arrays. To learn more about the add-in, visit FastExcel V4 SpeedTools

Using Excel Dynamic Arrays to generate a list of prime numbers in Excel. Also in this video: Creating a Custom List of numbers. Quickly converting numbers to text using TRIM instead of TEXT.

Take a look at a few examples of the new SEQUENCE function in Excel. Generate a long sequence of numbers quickly. Generate 52 trailing weeks. Generate a forward-looking calendar in Excel with two formulas.

So - you are ready to start including the new Dynamic Array formulas in your VBA project. Except, they never work. Why is Excel wrapping your formulas in an at @ sign? Argh. It is for backwards compatibility. Today, the insider tip on how to right forwards-compatible code and easily enter array formulas.