Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Using Array Formulas in Excel (Read 15915 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Using Array Formulas in Excel
18.06.06 at 11:09:03
 
Using Array Formulas in Excel
 
Array formulas are a powerful feature of Excel. In this topic I would like to explain what they are and why they are so useful.
 
Array formulas can work on ranges of cells. I use it most for looking up and conditionally sum or count items. You will say, but isn't that what countif and sumif is doing? Yes, true but it will get more difficult when you have multiple conditions.
 
An array formula is entered just like a normal formula. However when you have entered the formula you press Shift-Ctrl-Enter. Excel will put curly brackets around the formula like this:
{=SUM(($A$1:$A$10=1)*1)}
 
Please note that you do not enter the curly brackets {}. When you press Shift-Ctrl-Enter, Excel puts them around the formula.
 
I will explain the array formulas by the following example. Suppose I have some numbers in A1 to A10. If I want to count how many values in that range are equal to 1 I can use the formula =countif(A1:A10,1).
The equivalent array formula is {=SUM(($A$1:$A$10=1)*1)}
Explanation:
$A$1:$A$10=1 results in a vector where there is a 1 for a true and a 0 for a false. I multiply this by 1 to get the values and then sum the ones together to get the count.
 
Why doing this so difficult if you can do the same soo easially with countif. Well, you can expand this with other conditions very easy.
 
Suppose you want to check how many values are between 1 and 5. You would use {=SUM( ($A$1:$A$10>1)*($A$1:$A$10<5)*1)}
There are now two vectors with results. ($A$1:$A$10>1) results in vector with 10 items where there is a 1 where the value in A1:A10 matches the condition and where there is a 0 where the condition is not true. Equally the item ($A$1:$A$10<5) has 10 results, 0 or 1 depending on whether the condition is false or true. Now by multiplying these two sets of results, items 1 will be multiplied, items2 will be multiplied, and so on. The resulting vector contains a 1 where both conditions were true and it has a 0 where at least one of the conditions was false.  
Now, the SUM will add all the ones together to give the count we require.
 
See also:
Excel array formulas that give an array as result
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1150622308
Back to top
 
« Last Edit: 18.06.06 at 11:19:10 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Using Array Formulas in Excel
Reply #1 - 13.11.06 at 08:54:31
 
If the array shows horizontally you can transpose it to show vertically with the transpose Excel function.
 
{ =Transpose(MyArrayFormula()) }
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Using Array Formulas in Excel
Reply #2 - 17.12.09 at 15:33:17
 
Some handy shortcuts when using Excel array formulas.
 
Creating an array formula:
The smallest array is 1x1 (one cell) so if you just have one cell selected you can already enter the array formula and pressing Ctrl+Shift+Enter will actually cast it into an array function. This will make the initial typing of an array function much easier as changing an array function requires the selection of the whole array. If it is only one cell you never select an incorrect range of cells.
 
Enlarge the number of cells containing the array formula:
Expanding an array is easiest done by selecting the top left cell of the array and then to select a range of cells expanding beyond the previous size of the array. Pressing F2 will enter the formula editing mode. Press Ctrl+Shift+Enter to apply the formula for the new range of cells.
 
Reduce the number of cells containing the array formula:
Making an array smaller is not easily done. At least I have not found a way to do that esily. However this procedure works for me. If you try to delete part of an array Excel complains:
 
You cannot change part of an array
 
This works however. Select the part of the array you want to keep. Copy it and paste it somewhere. Then remove the original array. Then cut and paste the copied cells back. The net result is that you have made the range of cells containing the array formula smaller...  
 
Selecting all cells containing the array function
Selecting the complete array can sometimes be difficult especially when the array function returns a blank. There is a keyboard shortcut luckily. Hold down Ctrl+Shift and double click a cell of the array and the whole array will be selected by Excel.
Back to top
 
« Last Edit: 18.12.09 at 08:26:10 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1