Conitnuing patterns in Microsoft Excel

Forums: Computers
Email this Topic • Print this Page

So I'm tryin to make a large chart of numbers. My chart looks like this:

Code:

      _2_    _3_    _3_    _4_    _4_    _4_



2|    2-2    3-2    3-2    4-2    4-2    4-2

3|    2-3    3-3    3-3    4-3    4-3    4-3

3|    2-3    3-3    3-3    4-3    4-3    4-3

4|    2-4    3-4    3-4    4-4    4-4    4-4

4|    2-4    3-4    3-4    4-4    4-4    4-4

4|    2-4    3-4    3-4    4-4    4-4    4-4

Now, my numbers continue far past what I've shown, so I am looking for a way to fill in the middle efficiently. I know about the Fill command, but it doesn't do exactly what I want. The formula is the number in the column minus the number in the row, so when I shift, for example, the (2-2) function to the right, the next formula is (3-(2-2)) rather than (3-2).

Does anybody know how I can get this chart to do what I want?

Topic Stats
Top Replies
Link to this Topic

Type: Discussion • Score: 1 • Views: 715 • Replies: 1

No top replies

Re: Conitnuing patterns in Microsoft Excel

Josheva wrote:

So I'm tryin to make a large chart of numbers. My chart looks like this:

Code:_2_ _3_ _3_ _4_ _4_ _4_ 2| 2-2 3-2 3-2 4-2 4-2 4-2 3| 2-3 3-3 3-3 4-3 4-3 4-3 3| 2-3 3-3 3-3 4-3 4-3 4-3 4| 2-4 3-4 3-4 4-4 4-4 4-4 4| 2-4 3-4 3-4 4-4 4-4 4-4 4| 2-4 3-4 3-4 4-4 4-4 4-4

Now, my numbers continue far past what I've shown, so I am looking for a way to fill in the middle efficiently. I know about the Fill command, but it doesn't do exactly what I want. The formula is the number in the column minus the number in the row, so when I shift, for example, the (2-2) function to the right, the next formula is (3-(2-2)) rather than (3-2).

Does anybody know how I can get this chart to do what I want?

If I'm understanding you correctly, your formulae change if either you go up or down in a column or if you go side to side in a row.

Therefore the formula in cel b7 is the same as the one for cel c7 except for the different column heading used in the formula. I know it's really not the column heading but I think you know what I'm talking abouit.

Let's say that your spreadsheet

Code:

      _2_    _3_    _3_    _4_    _4_    _4_



2|    2-2    3-2    3-2    4-2    4-2    4-2

3|    2-3    3-3    3-3    4-3    4-3    4-3

3|    2-3    3-3    3-3    4-3    4-3    4-3

4|    2-4    3-4    3-4    4-4    4-4    4-4

4|    2-4    3-4    3-4    4-4    4-4    4-4

4|    2-4    3-4    3-4    4-4    4-4    4-4

starts at cel a2 (with the heading row at row1), so in cel a2 is the formula for 2-2, then in cel b2 is the formula for 3-2 and the cel in a3 is the formula for 2-3 and so on.

Place the following formula is cel b2: =B1-$A$2
in cel b3: =B1-$A$3
in cel b4: =B2-$A$4
etc. you can see the pattern.

Then select all of the cels in column b that you wish to copy over. That is, these are the cels with the formulae, so don't include the heading row's cel.

Then place your cursor in the lower right corner of cel b4 (or whatever the bottom row is for all of the cels with these formulae in them) so that it becomes a black cross. Holding down the left mouse button, click and drag it to the right as far as you need cels in the spreadsheet.

Release the mouse button.

That should do it; I've tried it and it works. You could do the same thing in reverse with the columns instead of the rows.

0 Replies

Conitnuing patterns in Microsoft Excel

Related Topics

Quick Links

My Account

able2know