Excel array functions

Question

Where can I find a complete list of Excel functions that support array formulas? For example, I am having issues with using array formulas with IMPRODUCT (well, what I am actually looking for is a complex equivalent for SUMPRODUCT)&nbsp;and IMABS, and I can't find a full list of functions that support array formulas, or even a note for a given function on whether or not it supports array formulas.

detlef_lewin · Answer

Michael,&nbsp;Mike Girvin mentions the functions that can't handle array calculations in some of his videos and I believe in his book "Ctrl+Shift-Enter Mastering Excel Array Formulas".&nbsp;

michael reshko · Answer

Thank you for your reply. I am not sue, however, that I understand what "book" you are talking about. I am expecting for this information to be documetned by Microsoft in Excel documetnation / help pages, but I could not find it. Is this not even documetned by Microsoft?...

sergeibaklan · Answer

Michael, i don't think such documentation exists since every function in Excel supports array calculations, directly or indirectly -through array formula or in combination with other functions. For example, does ABS supports array calculations? Definitely, =SUM(ABS({-1,2,-3}) returns correct result.&nbsp;&nbsp;Array is just another entity like cell value or range on which you could perform the calculations. Here are very few exceptions where you never can use arrays, e.g. for range argument in functions like SOMEIF().&nbsp;All the rest is tips and trick and/or common sense&nbsp;in how to use this or that function with the array.

detlef_lewin · Answer

Michael,&nbsp;I have not seen a documentation by Microsoft ... yet.I put a link to Mike Girvin's book in my first answer.According to Mike there are only about five or six functions which can't handle array calculations - so all the other function can handle array calculations.&nbsp;

Forum Discussion

Excel array functions

4 Replies

Resources