Autofill Incorrect Results

[Data123]

Hi I have three cells in one column with formulas shown below.

=((Data!B2-Data!B6)/Data!B6*100)

=((Data!C2-Data!C6)/Data!C6*100)

=((Data!D2-Data!D6)/Data!D6*100)

when I highlight the three cells and use the black cross on the bottom right hand corner of the last cell, the autofill offers this formula for the next cell:

=((Data!B5-Data!B9)/Data!B9*100) which is incorrect.

How can I fix this please?

 

[Joe4]

Autofill is working exactly as expected. It does not detect "patterns" in formulas like that (only hard-coded values).

When copying formulas down, if you have not locked the row reference, it will move the ROW reference down the same number of rows.
It will not touch the column reference if you are autofill down a single column.

Autofill won't do what you want on your current formula.
If you want it to increase COLUMNS in your formula, as you move down ROWS, you will need to use a different formula method, such as OFFSET or INDIRECT (or perhaps one of the newer functions).

See here for details on OFFSET: OFFSET function - Microsoft Support

Here is a simple example of showing how you can put it to work (I just used a single cell reference formula for simplicity):

In the beginning we have hard-coded values in cell B2:F2.
We want to put the first value in B4, and copy down and have it reference the values in the cells going across row 2.
So, we enter this formula in cell B4 and copy down to B8:

=OFFSET($B$2,0,ROW()-4)

It makes use of the "ROW()" function, which returns the row number of the row this formula is found in (that is how we get the incrementer as we move down the row).

 

[Data123]

Thanks! Hmm this is only the third workbook I have ever created and they are all about 99% similar, other than the data being referenced. The other two workbooks have the exact same formula but since the data it's referencing is setup slightly different the letters in the formulas are different.

However, I used the auto fill for those previous workbooks and it worked perfectly? May I ask why you think that is?

See below the other workbook formulas. The term Data is referencing a tab in the workbook that contains the data.

 

[Data123]

I meant to write, However, I used the auto fill for those previous workbooks FORMULAS mentioned in the image capture and it worked perfectly? May I ask why you think that is?

 

[Joe4]

If you try deleting all the rows but the first one currently on that sheet, and re-try your autofill, does it still work?
If not, I am thinking you did not originally used the autofill to populate it, and maybe used some sort of VBA or other method.

 

[Data123]

If you try deleting all the rows but the first one currently on that sheet, and re-try your autofill, does it still work?
If not, I am thinking you did not originally used the autofill to populate it, and maybe used some sort of VBA or other method.

Thanks, I deleted rows two and three, then used autofill on cell one only. It did not correctly autofill.

The two older workbooks were autofilled for sure. I have over a hundred cells in each of the 15+ columns and remember how nice it was that it autofilled correctly.

The other attributes that are different is the older workbook that autofilled correctly used data in the data tab from Stockhistory and the cells were inputted horizontally. I am using a copy of the old workbook for the new workbook that does not autofill correctly? Could the copy of the old workbook create a confusion if I write a new formula, seen in the first post here?

Also, the new workbook has the data in the data tab shown in cells going vertically.

 

[Joe4]

Thanks, I deleted rows two and three, then used autofill on cell one only. It did not correctly autofill.

That is what I expect, as that is typical Autofill behavior.

If you got it to work any other way, I would love to know how you did it, as I don't know how that would be done (some sort of use with Transpose or VBA, maybe)?
Newer version of Excel, like 365, have new functions too, like TOROW and TOCOLUMN, though I am not sure how they would be used to do this. It certainly wouldn't create the formulas you talk about.

The only way I can think of to populate those formulas in that manner is to use VBA to populate them.

 

[RoryA]

However, I used the auto fill for those previous workbooks and it worked perfectly? May I ask why you think that is?

Those three formulas refer to cells all on the same row, so when you copy them down, the row numbers will all adjust by 1 each time. The same is not true of your current formula.

 

[Joe4]

Those three formulas refer to cells all on the same row, so when you copy them down, the row numbers will all adjust by 1 each time. The same is not true of your current formula.

Thanks, I missed that.

Rory, am I correct in what I am saying? I have never seen Autofill work the way they mention, have you?

 

[RoryA]

It would never work for the formula originally posted here, no.