problem with autofill of a formula 2

[WillieLoMain]

2 sheets in a workbook - MASTER with the data and WEEK where I am trying to generate an auto fill.

In WEEK I have populated the first 2 cells I want with the following formula:

Cell B2: =SUM(Master!C3:C9)
Cell B3: =SUM(Master!C10:C16)

When I select Cells B2 and B3 and pull a fill to B4 I get:

=SUM(Master!C5:C11)....... but I expect =SUM(Master!C17:C23)

This must be simple but for the life of me I am lost - suggestions comments etc welcome and appreciated

 

[Dreamboat]

Hm. Don't know if it will help, 'cause I didn't test it, but you really need THREE items to start a "series" like that.

Anne Troy

http://www.amazon.com/exec/obidos/tg/detail/-/0972425845/104-9778384-2391158?v=glance&s=books

 

[mbarron]

You are getting exactly what should be expected of Excel.

There are two types a cell references into Excel, relative references (A1) and absolute references($A$1, A$1, or $A1) When you use a relative reference Excel iterprets the entry locatin in relation to where it references. For example, if your data is in cell A1 and your formula in cell C2 is =A1, Excel thinks of this a the cell one row up and two columns to the left. So if you copy this (pull a fill) to C3 your formula in C3 is =A2 or one row up and two columns to the left.

Check the Help file for more explaination on Relative and Absoute references.

Here's what you can do. In B2, put your =SUM(Master!C3:C9). Highlight B2, B3, and B4. Now do your pull to fill as far as you would need. (trust me, this will work as long as you have enough vertical room or do it a couple times) After you've pulled - keep everything highlighted. Press F5 (or goto Edit/ Goto or press Ctrl+G) press the Special button. Choose Blanks, press OK. This will select the blank cells in the column. Right click in one of the highlighted cells and choose Delete... Shift the cells Up. Your formula should be as you had originall expected.







Mike

 

[mbarron]

Anne,
That gives the same results as what Willie is currently getting. You may be thinking of the linear best-fit trend dragging.


Mike

 

[WillieLoMain]

Mike: I see what you are getting at here and it will work as you have said (except that I have to have 6 spaces between not 2).

This sure seems like much to do for something that seems so simple - am I using the program in some unique way or do people do this kind of thing with excell all the time?

Ann. I have tried this with 3+ in the trend and it does not matter.

Other suggestions appreciated of course

 

[mbarron]

Sorry, my test had 3...

You're not using it in a unigue way, but you were expecting unique results.

Mike

 

[xlbo]

You can't drag formulae as a non standard series like that - if you move 1 cell down, the references will move 1 cell down. Same for across - how would excel "know" that you want to sum in groups of 6 ???

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[KenWright]

With your data in the exact ranges you have specified, in B2 put the following and copy down:-

=SUM(OFFSET($C$3,(ROW()-2)*7,,7))

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

Only thing to watch with the last one is that if you delete row 1 or add more rows prior to the current row 2 then it bombs. The following amendment will cater for that:-

=SUM(OFFSET($C$3,(ROW()-ROW($C$2))*7,,7))

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[WillieLoMain]

KenWright

Incredulous as it may seem - even with the answer I cant figure out how this works - but it does and it, to a simple mind like mine perhaps, seems BRILLIANT.

Thank you much

 

[KenWright]

OK then, lets see if we can bring some enlightenment

=SUM(OFFSET($C$3,(ROW()-ROW($C$2))*7,,7))

The SUM bit should be obvious, and will sum whatever range you pass to it via any valid method, and in this case we are simply passing it a a 7 cell range that increments by 7 cells every time you move down one, Q is, how?

This all lies in the OFFSET function, which has the following syntax:-

=OFFSET(reference,rows,cols,[height],[width])

The reference argument is the starting point for the function, and the last two arguments are optional. The way this works, using just one of the arguments (row) as an example is as follows:-

In say cell A1, if you put the following formula

=OFFSET($B$1,1,0)

this is the same as saying, start at B1 and Offset 1 row down, which gives you B2

=OFFSET($B$1,1,0) = B2
=OFFSET($B$1,2,0) = B3
=OFFSET($B$1,3,0) = B4
=OFFSET($B$1,4,0) = B5
=OFFSET($B$1,5,0) = B6

or if I were to use the column argument,

=OFFSET($B$1,0,1) = C1
=OFFSET($B$1,0,2) = D1
=OFFSET($B$1,0,3) = E4

or with both

=OFFSET($B$1,1,1) = C2
=OFFSET($B$1,1,2) = D2
=OFFSET($B$1,3,2) = D4

Now, if you then factor in other functions such as ROW(), that will increment by 1 each time you move down a row, watch what happens

In A1 =ROW() = 1
In A2 =ROW() = 2
In A3 =ROW() = 3

so, therefore, if I put *7 on the end

In A1 =ROW()*7 = 1*7 = 7
In A2 =ROW()*7 = 2*7 = 14
In A3 =ROW()*7 = 3*7 = 21 etc (See the pattern)

This means that by tying those two together, eg:-

In A1 =OFFSET($B$1,((ROW()-1)*7),0) = OFFSET($B$1,0,0) = B1
In A2 =OFFSET($B$1,((ROW()-1)*7),0) = OFFSET($B$1,7,0) = B8
In A3 =OFFSET($B$1,((ROW()-1)*7),0) = OFFSET($B$1,14,0) = B15

This shows you how you can increment by 7 each time, or whatever number you come up with, BUT, what you now need is a way to tell it that the range you want is 7 cells from and including those cells B1, B8, B15 etc, so that this range can then be passed to the SUM function.

Well, remember those optional arguments I mentioned in OFFSET, one of them was height (the last one), and if you specify this as say 7, then it will create a 7 cell range from and including whatever cell the other OFFSET arguments have gotten you to, eg the B1, B8, B15. So, taking the 3 formula lines from above here:-

In A1 =OFFSET($B$1,((ROW()-1)*7),0,,7) = OFFSET($B$1,0,0) = B1:B7
In A2 =OFFSET($B$1,((ROW()-1)*7),0,,7) = OFFSET($B$1,7,0) = B8:B14
In A3 =OFFSET($B$1,((ROW()-1)*7),0,,7) = OFFSET($B$1,14,0) = B15:B21

Note the two commas together ,, as this simply menas that you are not using the width argument, but if you don't put in the comma, it will assume that the 7 is a width argument and not a height argument.

All these get passed to the SUM function and you then end up with in this case:-

=SUM(B1:B7)
=SUM(B8:B14)
=SUM(B15:B21) etc

Hopefully this helps you somewhat

Regards
Ken.......................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------