Basic array formula example

Basic array formula example

In the example below, we want to find the maximum change in temperature over seven days:

The formula in F5 is:

=MAX(C5:C11-D5:D11)

This is an array formula that returns a single result.

Working from the inside out, we first subtract the low temps from high temps:

C5:C11-D5:D11 // array operation

Each range contains 7 values, which we can expand into arrays like this:

{86;84;89;87;82;85;88}-{69;65;57;62;70;59;59}

This is called an array operation. We are working with multiple values, and the result after subtraction is a new array with 7 values, where each value represents the change in temperature on the given day:

{17;19;32;25;12;26;29} // new array

The new array is returned directly to the MAX function which returns the largest value:

=MAX({17;19;32;25;12;26;29}) // returns 32

You can see that this array formula is actually quite simple!

Traditional Excel - complication and danger

The problem arises when we enter the formula. In "Traditional Excel" (currently, every version of Excel except Office 365), this formula must be entered with control + shift + enter. When entered this way, Excel will display curly braces in the formula bar like this:

{=MAX(C5:C11-D5:D11)}

These curly braces tell you that Excel is handling the formula as an array formula. In other words, Excel is "letting you" work with multiple values.

To most users, that's pretty strange and confusing. But it gets worse. 

If you (or someone else) forgets to enter the formula with control + shift + enter, the same exact formula may return an incorrect result.

For example, the formula above without control + shift + enter will return 17, the change in temperature on Monday. This will be a "silent failure" – no warning will occur. The formula will simply stop working correctly.

Obviously, formulas that return incorrect results are bad news :)

Dynamic Excel - simplicity and clarity

The great thing about the Dynamic Array version of Excel, is that array formulas just work. You don't have to use control + shift + enter with any array formula.

Even better, a formula that returns multiple multiple values will spill these values onto the worksheet. This makes array formulas much easier to understand, because it's obvious when a formula is returning more than one value.

In contrast, the same formulas in previous versions of Excel will display only one result in a single cell, no matter how many values are actually returned. 

The bottom line is that working with array formulas in Excel is now easier and more intuitive than ever. You can now use array formulas whenever you like, without worrying about fancy syntax requirements.

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1