Array Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Michl - 17 Mar 2006 18:22 GMT

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

Reply to this Message

Ron Coderre - 17 Mar 2006 18:36 GMT

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

> Is there a function that will create an array based on values in two
> other cells?
[quoted text clipped - 12 lines]
>
> - John

Reply to this Message

Harlan Grove - 17 Mar 2006 19:12 GMT

Ron Coderre wrote...

>Try something like this:
>
[quoted text clipped - 5 lines]
>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

Reply to this Message

John Michl - 17 Mar 2006 22:53 GMT

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

Reply to this Message

John Michl - 17 Mar 2006 23:11 GMT

Well, now it is working but I still don't understand how it works.

- John

Reply to this Message

Ron Coderre - 18 Mar 2006 00:20 GMT

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

> 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
[quoted text clipped - 4 lines]
>
> - John

Reply to this Message

John Michl - 20 Mar 2006 16:16 GMT

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

Reply to this Message