Math Is Fun Forum

Daniel123

Member

Registered: 2007-05-23

Posts: 663

excel

My excel skills are very limited, and I would like to know a quick way to do this:

In cell E40 I want the answer to E21 + D22
      in F40: F21 + D23
         G40: G21 + D24
         H40: H21 + D25

etc...

How do I do this?

Thanks

Daniel123

Member

Registered: 2007-05-23

Posts: 663

Re: excel

Anyone.. I need an answer to this really quickly!!!

Thanks

NullRoot

Member

Registered: 2007-11-19

Posts: 162

Re: excel

A very quick way is to pick two new rows (let's say 41 and 42).
At E41 make the cell "=E$21" and drag it to the right as far as you need to go. The $ will lock the '21' and when you drag it, it will assume you want to increase the column reference only.
At E42, make the cell "=$D22" and drag it to the right as far as you need to go. The $ will lock the 'D' this time and increment the row reference instead.
Then, for E40, make it "=E$41+E$42" and drag it across to the right. This should increment the column references and indirectly reference the correct cells that you need.

---

Trillian: Five to one against and falling. Four to one against and falling… Three to one, two, one. Probability factor of one to one. We have normality. I repeat, we have normality. Anything you still can’t cope with is therefore your own problem.

mathsyperson

Moderator

Registered: 2005-06-22

Posts: 4,900

Re: excel

At E42, make the cell "=$D22" and drag it to the right as far as you need to go. The $ will lock the 'D' this time and increment the row reference instead.

Wouldn't that just make it "=D22" all the time? Dragging to the right doesn't change the row number.

---

Why did the vector cross the road?
It wanted to be normal.

mathsyperson

Moderator

Registered: 2005-06-22

Posts: 4,900

Re: excel

The problem here is that you've got one set of data as a row, and one set as a column.
There's a way to convert row data to column data (and vice versa) but it's a bit complicated.

Searching for "transpose" in Excel's help files will show you how to do that. Make sure you read the note on how to enter things as array functions.

Edit: NullRoot's way of transposing is probably easier.

---

Why did the vector cross the road?
It wanted to be normal.

NullRoot

Member

Registered: 2007-11-19

Posts: 162

Re: excel

At E42, make the cell "=$D22" and drag it to the right as far as you need to go. The $ will lock the 'D' this time and increment the row reference instead.

Wouldn't that just make it "=D22" all the time? Dragging to the right doesn't change the row number.

Just tried it and Mathy's right. Sorry about that.

Do this instead of pasting "=$D22" into E42:

Use a free column and drag "=$D22" down as far as you need to. Select the filled in cells and Copy.
Click once on E42, go to Edit> Paste Special.
In the menu that comes up, choose "Formulas" under the Paste section, "None" under the Operation section, and make sure the "Transpose" box is checked at the bottom.

Click OK and then do the "=E$41+E$42" step.

---

Trillian: Five to one against and falling. Four to one against and falling… Three to one, two, one. Probability factor of one to one. We have normality. I repeat, we have normality. Anything you still can’t cope with is therefore your own problem.

Daniel123

Member

Registered: 2007-05-23

Posts: 663

Re: excel

Thanks both of you, you were a great help.