adding variable arrays

[soce]

Lets say i have 2 one dimensional arrays. They are both 1 x 24. I call them A and the other B on the excel sheet. Now what is the simplest way to make a third array named C = A + B? Excel seems to have no trouble letting me set A or B equal to C but when I try to do addition it pukes all over me. I am hoping that I don't have to add the values up part by part. What I am saying is, I am wanting to get around doing

for i = 1 to 24
C(i) = A(i) + B(i)
next i

rather, can't I just do

C = A + B somehow?

Thanks

[Leith Ross]

Hello Soce,

You could place the arrays on a worksheet, and use worksheet array functions to add the values, or use a pivot table. In VBA the only option available is to loop.

Sincerely,
Leith Ross

[ChemistB]

Hi Soce,

What you need to do is highlight a number of cells equal to your other two arrays (1 x 24) and type in

= A + B and then CNTRL SHFT ENTER which enters the values as an array.
You can then name the highlighted area (using the name box) if you desire.

Is that clear?

ChemistB

[shg]

There's no need for an array formula, and it can make life difficult (like not being able to insert a row); I would do what Chemist recommends to the point of entering the formula, and then do Ctrl+Enter instead of Ctrl+Shift+Enter.

Excel is smart enough to understand that

=A + B

are references to elements of the arrays corresponding to the row in which the formula appears, rather than the arrays in their entirety -- it's called an implicit intersection.

There's nothing special about group-entering the formula; you could equally enter it in one cell and drag it down the others.

[ChemistB]

Hi shg

I tried to follow your instructions and got #Value. Here's what I did

In A1:D2, I put
1, 2, 3, 4
5, 6, 7, 8 I named it Table1

In A4:D5 I put
5, 10, 15, 20
25, 30, 35, 40 and named it Table2

Then in F1, I tried = Table1 + Table2 with Cntrl Enter. I am using Excel 2000 if that makes a difference. Thanks

ChemistB

[mudraker]

soce

Please read forum rules & wrap your VBA code

A thread with the rules is available at the top of each forum or see links below

If you do not understand the VBA code wrap instructions have a look at my last reply in this thread
http://www.excelforum.com/showthread.php?t=583950

[shg]

I tried to follow your instructions and got #Value.

The OP had one-dimensional ranges; yours are two-dimensional. That won't work for an implied intersection.

Additionally, the cell containing the formula must contain a row or column in common with the referenced range. Here's an example that illustrates. The top row of the table is named "T", and the left column is named "L". So this formula,

Please Login or Register  to view this content.

gives a multiplication table:

Please Login or Register  to view this content.

[ChemistB]

Thanks SHG, I hadn't known about that property of Excel.

ChemistB

[shg]

You're welcome. It's a cool tool ...