**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