Replace Ctrl+Shift+Enter with Dynamic Arrays - KING OF EXCEL

Thursday, January 9, 2020

Home Mr King Excel Tips Replace Ctrl+Shift+Enter with Dynamic Arrays

Replace Ctrl+Shift+Enter with Dynamic Arrays

January 09, 2020 Mr King Excel Tips,

Replace Ctrl+Shift+Enter with Dynamic Arrays

Before dynamic arrays, people would use these crazy Ctrl+Shift+Enter formulas.

Say that you have a friend who is superstitious about Friday the 13th. You want to illustrate how many Friday the 13ths your friend has lived through. Before Dynamic Arrays, you would have to use the formula below.

Before dynamic arrays, the formula to calculate the number of Friday the 13ths between B1 and B2 is =SUMPRODUCT(
--(DAY(ROW(INDIRECT(B1&":"&B2)))=13),
--(WEEKDAY(ROW(INDIRECT(B1&":"&B2)),2)=5)) entered with Ctrl+Shift+Enter.

The same formula after dynamic arrays is still complicated, but less intimidating:

With dynamic arrays, the formula is =SUMPRODUCT(
(DAY(SEQUENCE(B4,,B3))=13)*
(WEEKDAY(SEQUENCE(B4,,B3),2)=5))
entered without Ctrl+Shift+Enter.

Another example from Mike Girvin's Ctrl+Shift+Enter book is to get a unique list.

A helper formula in D2 counts the number of unique items in a list. Then, a formula returns the unique list in D5. See the next screenshot for the formula.

Here is the formula. I won’t try to explain it to you.

This was the insane formula to get the unique list before Dynamic Arrays: =IF(ROWS(D$5:D5)>$D$2,"",
INDEX($B$2:$B$146,
SMALL(IF(FREQUENCY(IF($B$2:$B$146<>"",
MATCH($B$2:$B$146,$B$2:$B$146,0)),
ROW($B$2:$B$146)-1),
ROW($B$2:$B$146)-1),
ROWS(D$5:D5))))

The replacement formula with dynamic arrays is =UNIQUE(B2:B146).

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

Crafted with by TemplatesYard