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