Array Function

J

John Michl

Is there a function that will create an array based on values in two
other cells?

Give:
A1 = 3
A2 = 10

Create:
A3 = {10,10,10}

If either A1 or A2 changes, then the result of the array in A3 would
change.

Thanks.

- John
 
R

Ron Coderre

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].

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
H

Harlan Grove

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].
Click to expand...
....

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
 
J

John Michl

Slight problem, Ron and Harlan. In cell A1 I have a formula that
determines the number of elements. Your formulas do not work if there
is a formula in cell A1. If I replace the formula with an actual
value, your formulas do work. I'm not sure understand why your
formulas work so I can't troubleshoot or modify. What purpose do the
ROW and INDEX functions play in your solutions?

- John
 
R

Ron Coderre

The ROW and INDEX functions are used to coerce Excel into creating a
sequential array of values (from 1 through the limit you entered in A1.

You can see how they work by doing this:
1)Edit the cell
2)Select this part of the formula: ROW(A1:INDEX(A:A,A1,1))
3)Press the [F9] key

If A1 contains 4, you'll see this: {1;2;3;4}

Press the [Esc] key to cancel the edits.

Change the value of A1 and repeat those steps.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
J

John Michl

Thanks, that helps.

Now, how can I reference this in a chart data range. If I edit the
formula and press F9, I see the array. However, I'm trying to use this
as Values in a Source Data range in a chart. In the chart, I'll edit
the Values and select the cell with the array but only the first value
appears in the chart. If I type in the array ={1;2;3;4} in the Values
field, it displays fine.

- John
 
Top