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
Excel array formulas that give an array as result (Read 3881 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Excel array formulas that give an array as result
18.06.06 at 11:18:28
 
Excel array formulas that give an array as result
 
In this topic I will explain how you can use array formulas in Excel to work on set of values and where the result is a set of values.
 
Suppose we have 2 lists of 10 numbers. One list is in A1:A10, the second list is in B1:B10. If I want to multiply the two numbers from each column I could write in C1: =A1*B1 and copy the formula in C1 to C1:C10.
 
You can use an array formula for this too. Select cells C1:C10 and enter the formula {=A1:A10*B1*B10}
Note that you do not enter the curly brackets {} but that you press Ctrl-Shift-Enter in Excel when you have typed for formula. Excel will then add the brackets and regard the formula as an array formula.
 
The nice thing about these array formulas is that you can easially put a condition into this statement. Suppose I want to multiply the numbers only when the value in A1:A10 is not 1. I would use:
{=A1:A9*B1:B9*(A1:A9<>1)}
Again, select C1:C10 and enter this formula. Now, where there is a value 1 in column A the result is 0.
 
See also:
Using Array Formulas in Excel  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1150621743/0
Back to top
 
« Last Edit: 18.06.06 at 11:19:33 by Gerrit-Jan Linker »  

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