Array Formulas.. Now this might be the first time most of you are reading about it.... So what is an Array formula…??
An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. Its like a small program in itself one which can have loops/ iterations and do calculations which can not be achieved using normal formulas.
- Karthik
Subscribe to:
Post Comments (Atom)
3 comments:
As it is not so easy to explain how Array formulas work. I will post some examples: some of my own and some examples from other...
This should make Array formula less intimidating..
Problem: Trim Extra spaces at the end (end only) of a text string
Solution: {=LEFT(A1,LEN(A1)-COUNT(IF(TRIM((RIGHT(A1,ROW($1:$10000))))="",1,"")))}
Example :
http://groups.google.com/group/ExcelFiles_India/browse_thread/thread/eca82104088f3a3a#
-Karthik
Problem: Find sum of first n highest or lowest values in a list.
Solution: See Example number 2 (Array_Formula_Example_2.xls)
Example :
http://groups.google.com/group/ExcelFiles_India/browse_thread/thread/eca82104088f3a3a#2
-Karthik
Problem : Extract the date at which the cumulative balance in a table becomes equal to or greater than a specific value.
Solution : {=INDEX(A2:A10000,MATCH(" ",IF(300>C2:C10000,ROW(C2:C10000)," "),0))}
Example:
http://groups.google.com/group/ExcelFiles_India/browse_thread/thread/6064c229d54ede7e#
-Karthik
Post a Comment