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.
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.
No comments:
Post a Comment