Array-enabled Formulas in Excel

Yesterday, I received one “support” case from my blog reader about array-enabled formulas in excel.

An Excel Array-enabled Formula performs multiple calculations on one or more sets of values (the ‘array arguments’) and returns one or more results.

Click here to download simple example

In the example, I use array-enabled formula to calculate “Median of P/E less than 17” and “Median of P/E in 2012 (with Array)”. For instance, to calculate “Median of P/E less than 17”, the formula is {=MEDIAN(IF(C2:C271<17,C2:C271))}. Can you see the difference? There is a {} bracket. {} bracket indicates array-enabled formula. Why do we need to enable array in this formula? C2:C271 is the full set of the data. If we want to specify condition, we have to tell excel to build a SUBSET of data that fulfill the condition. That subset of data is so called array.

How to enable array formula? Enter the particular cell that contains the formula, press this magic key: Press CTRL-SHIFT-Enter (ie. press the CTRL and SHIFT keys, and while these are pressed down, press the Enter key

Remember don’t simply enable array formula in all formula cells if you don’t need it. This will degrade performance of your excel if there are thousands of array formula.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s