Thread: Array Function
View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default Array Function

Ron Coderre wrote...
Try something like this:

A1: (number of array elements)
A2: (value to assign each element)

B1: =IF(ROW(A1:INDEX(A:A,A1,1)),A2)

Note: To commit that array formula hold down the [Ctrl][Shift] keys and
press [Enter].

....

Since you're entering this formula only into cell B1, when A1 is a
nonnegative number it'd always return the same result as the much
simpler =A2. When A1 is anything else, it'd return #VALUE!. The only
way to make it an array result is to enter it into multiple cells at
the same time as an array formula.

If the OP needed a term that could be used in longer formulas, it could
be reduced to

=A2*ROW(A1:INDEX(A:A,A1))^0