Скачать или смотреть Excel - Ctrl+Shift+Enter Formulas - Episode 2026

Microsoft Excel Tutorial: Old-style Array Formulas in Excel.

Welcome to episode 2026 of the MrExcel podcast, where we dive into my all-time favorite formula in Excel - the Ctrl+Shift+Enter formula! This formula opens up a whole new world of possibilities in Excel, known as Array Formulas. In this episode, we will be exploring the power of this formula and how it can be used to solve complex problems.

But before we get started, make sure to check out the entire podcast series by clicking the “i” in the top-right hand corner to access the playlist. Now, let's dive into the 30th topic in the book, where I just had to include my all-time favorite formula. This formula is not just useful for counting the number of Friday the 13ths, but it also allows us to perform 91896 calculations in just one little formula!

So how does this formula work? Well, it all starts with the INDIRECT function. This function allows us to concatenate or build a bit of text that looks like a cell reference. For example, if we have a prize wheel and we ask someone to choose between A, B, and C, we can use the INDIRECT function to return the corresponding prize stored in that cell. This function is similar to the @@ function in Lotus 1-2-3, but in Excel, it is known as INDIRECT.

Now, here's where things get interesting. We can use the INDIRECT function to point to a range of rows in Excel by concatenating two dates with a colon. This will give us an array of consecutive numbers, which we can then pass to the ROW function. This simple expression can turn into 31 values for a month, 365 values for a year, or even 86000 values for a larger range. And by using the WEEKDAY function, we can check each date to see if it falls on a Friday, and by using the DAY function, we can check each date to see if it is the 13th of the month. By multiplying these two arrays and using the SUMPRODUCT function, we can get the desired result.

But wait, there's more! In order for this formula to work, we need to press Ctrl+Shift+Enter instead of just Enter. This tells Excel to go into super formula mode, also known as array formula mode, and perform all the necessary calculations. And if you want to learn more about array formulas, I highly recommend my friend Mike Girvin's book, "Ctrl+Shift+Enter". It's an amazing book that will open up a whole new world of Excel formulas to you.

So there you have it, my all-time favorite formula in Excel - the Ctrl+Shift+Enter formula. It's a powerful tool that can perform thousands of intermediate calculations and solve complex problems. And remember, if you want to master this formula, make sure to get your hands on both my book and Mike's book. Thank you for tuning in to this episode of the MrExcel podcast, and I'll see you next time for another netcast!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


Table of Contents:
(00:00) Array Formulas in Excel
(00:24) Explanation of array formulas
(01:01) Recommendation for further reading on array formulas
(01:23) Introduction to the INDIRECT formula
(02:08) Example of using INDIRECT with dates
(03:22) Explanation of how Excel stores dates
(04:21) Example of using INDIRECT with WEEKDAY function
(05:23) Explanation of using INDIRECT with SUMPRODUCT
(06:09) Example of using INDIRECT to calculate Friday the 13ths
(07:35) Buy the Book!
(07:52) Clicking Like really helps the algorithm

This video answers these common search terms:
how do array formulas work in excel
how to array formula excel
how to enter a formula array in excel
how to use array formula in excel
how to use array formulas in excel
when to use array formula in excel
how to do an array formula excel
how to create array formula in excel
how to use an array formula excel
how to make an array formula in excel
how to type an array formula into excel
how to change an array formula in excel
what is an excel array formula
how do you enter an array formula in excel
how to create an array in excel formula
how to ctrl shift on excel
how to built cse formulas in Excel
formula with curly braces
enter formula with ctrl+shift+enter

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelformula #excelformulasandfunctions #excelarrayformula

An array formula can do thousands of intermediate calculations.
They often require you to press Ctrl+Shift+Enter, but not always.
The best book on array formulas is Mike Girvin's Ctrl+Shift+Enter.
How many Friday the 13ths happened between two dates?
Use -- to convert True/False to 1/0

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...