Simple Excel 97 Question

Suppose I have two columns of numbers in a worksheet. How do I add each number of the first column to each number of the second column? For example, if I have a 2x3 matrix of numbers [A1, A2, A3, B1, B2, B3], what I would like to do is to create a new worksheet containing this:



A1+B1 A1+B2 A1+B3
A2+B1 A2+B2 A2+B3
A3+B1 A3+B2 A3+B3

It should be simple, and I used to be able to do it, but I now totally forgot. TIA.

If I’m understanding your question, this should work. Enter the formulas in the first row exactly as follows:

=$A1+$B$1 =$A1+$B$2 =$A1+$B$3
You can then select these three cells and drag them down. Of course, you’ll have to make allowances for the sheet name of the source data (e.g., Sheet1!$A1+Sheet1!$B$1), but I think you know what I mean.

Good luck!

I am actually thinking something like a crosstab. I can do it manually for a small sheet, but what if I have several hundred numbers in each column? ::scared::

Please?

Well I guess it wasn’t such a simple question after all!

I could have sworn that there’s some kind of tool in Excel that does it, but I just couldn’t remember what it is. :frowning:

I don’t understand why you don’t like splatterpunk’s answer. It’s the correct answer. It doesn’t matter how many numbers are in each column. Just grab the corner of the selection and drag them down. It’s easy and quick.

It works well for my hypothetical example. It doesn’t work so well if I have tens let alone hundreds of numbers in each column.

Type =A1+B1 in the upper left cell. then copy and paste over the entire block. both the A number and the B number will change with reference to the pasted cell, and you should be okay.

That won’t work. Using this method would give Urban Ranger the following:



A1+B1  B1+C1  C1+D1
A2+B2  B2+C2  C2+D2
A3+B3  B3+C3  C3+D3


… clearly not what he/she is looking for.

Urban Ranger, it seems I am confused about what you are trying to do. Can you be a little more explicit? You mentioned a 2x3 matrix, so I gathered you only wanted to add 3 values to the first column.

Sorry. I thought I would use a simple example to illustrate my problem.

Okay, as I said, I have two columns of numbers in a worksheet:

A: A1, A2, A3. . .Am
B: B1, B2, B3. . .Bn

I would like to create a matrix C from these two columns, so that C{i,j} is the sum of A{i} + B{j}. My example is what it looks like when both A and B contain 3 numbers.

Excel 2000 (not sure about 97) lets you do array/vector type stuff if you hit… you read? ctrl-shift-enter when you are ready to enter your formula. I have a feeling this is what you are after, but I’m not sure. I’ll have to play around with it to see if I can get it to do exactly what you want, but I know it can be done that way.

It is a real kludge if you ask me, but there you have it. I have 97 at home so I’ll check it and see about reporting back if it works.

Thanks

A1+B1 A1+B2 A1+B3
A2+B1 A2+B2 A2+B3
A3+B1 A3+B2 A3+B3

A1+$B$1 A1+$B$2 A1+$B$3…copy and paste down the sheet

i also may not have picked up your question but this would give

A1
A2
A3
A4
A5 etc + B1
and so forth

100 10 110 120 130
200 20 210 220 230
300 30 310 320 330
400 410 420 430
500 510 520 530
600 610 620 630

ran this off to check it out
does this conform

sorry i will put in the spaces
100 10 110 120 130
200 20 210 220 230
300 30 310 320 330
400 410 420 430
500 510 520 530
600 610 620 630
:eek:

I can think of a couple of ways of doing it with formulae…

The OFFSET function lets you specify coordinates directly. ROW and COLUMN give the row and column of the cell they are in. Try something like:

=OFFSET(Sheet1!$A$1,COLUMN()-1,0)+OFFSET(Sheet1!$B$1,ROW()-1,0)

Also, the first term in each expression is A(current row+const), so could be written =$A1, a relative reference, in the top left cell, and then dragging will give the appropriate values.

Or, and this is probably the most natural, you could put osme row/column headings in and use hlookup and vlookup… I can’t be bothered to look these up in the help, but if you do, they’ll proabbly do what you want.

It does sounds a bit like a crosstabby thing, but I don’t know anything about those, so I don’t know :slight_smile:

It would be easier if I could somehow turn a column of numbers into a row. But there’s no automatic way to do it that I could find.

Paste Special | Transpose

Be careful, you might run into the Excel column limit: 255, IIRC.

I played more with the ctrl-shift-enter function, and I don’t think it will do what you want. It does Matrix Multiplication great, though.

If your columns of data are less than 255, you can do what Terminus Est said:

Move data from A1:Ax to A2:Ax+1
Select A1:Ax
Edit -> Cut
Move Cursor to A2
Edit -> Paste

Move data from B1:Bx to B1: (??)1
Select B1:Bx
Edit -> Cut
Move Cursor to B1
Edit -> Paste Special -> Transpose

A1 should now be empty.

In B2, enter this formula:
=$A1+B$1

Move cursor to B2 -> Edit -> Copy

Highlight B2 through the last cell in your matrix

Edit -> Paste

If this isn’t clear, let me know. I tried this, and this does work.

Another Hint:
To quickly select all the occupied cells in a continuous column, move the cursor to the first cell, then press Shift, Down arrow, then End. Dragging works, but if you have really long columns it can take a while.