1. ## Mystery autofill

Hi

I have three adjacent cells, containing:
1
2
34

If I highlight all three cells, then drag the autofill to say, another 10 cells, my first 'new' value is 45.33333, then 61.83333, then 78.33333 etc.
It doesn't matter whether the start cells are in rows (and then 'autofill-dragged-down'), or in columns (and then 'autofill-dragged-across), or in Excel2003, 2007, 2010, 2013.
The results are the same.
I just wanted to know what the rule is.
(I've also tried 2,4,56)

zeddy

2. Zeddy,

As Artie Shaw used to say: "Veeeery Interesting...Veeeery Interesting"

The formula in D2 below shows how Excel gets the difference to add to the numbers in the sequence EXCEPT for the first calculated value A4. The average of the three numbers is 12.33333 as shown in C1 but where does Excel get 11.33333??????
FillProblem.JPG

3. Hi RG

Yes, it's the first 'autofill' value that puzzles me.
I wasn't expecting it.
How am I going to explain it to my wife?

zeddy

4. it appears to be the last value plus one-third of (the last value - 2 * the first value + the second value)

I have no idea why though!

5. Hey Guy's,

I guess the feature is intended for regular interval patterns as Excel has no problem with them. Just know to avoid irregular patterns.
I tried to give it more to work with in this example but same problem with irregular patterns while regular interval patterns work just fine.
FillProblem.JPG
HTH

6. Something to confuse you even further (or possibly help).

In the Context options for a Fill operation of the type you are referring to, you will find the following options:

Linear - creates a series that is calculated by adding the value in the Step value box to each cell value in turn.
Growth - creates a series that is calculated by multiplying the value in the Step value box by each cell value in turn.
Date - creates a series that fills date values incrementally by the value in the Step value box and dependent on the unit specified under Date unit.
AutoFill - creates a series that produces the same results as dragging the fill handle.

7. Hi Martin

Thanks for the info re: Home>Editing>Fill>Series on the Excel Ribbon.

I like Rory's answer so far- his formula correctly 'predicts' the 4th term, i.e. with the first 3 terms as 1,2,34, the autofill-dragged 4th term is 45.33333.
Curiously, rory's formula also 'predicts' the 7th term onwards, but doesn't get the 5th and 6th term (????)
Using a different set of first three terms, rory's formula still gets the 4th term right, and still gets the 7th term onwards (again), but still doesn't get the 5th and 6th term. (For example, starting cells with {1.1, 2, 34} or {3,5,78} )

I'm still trying to work out what is going on.

zeddy

8. Hi All

Mystery solved!
I worked out what Excel is doing.

The Autofill is using the Excel Forecast Function to determine the 'fill' values, using the existing values entered as the known 'y' values and the item position number as the 'known x-values'.
I can also now understand why rory's formula doesn't match the data-filled-values for the 5th and 6th terms, yet matches all others. By 'not anchoring' the start-cell in the equivalent Excel Forecast formula, I can replicate all of rory's values.

see attached file.

Ah well. It's good to know where those autofill values came from.

zeddy

9. ## The Following User Says Thank You to zeddy For This Useful Post:

RetiredGeek (2013-05-03)

10. Thank you for closing out this mystery.

Now to find a way to exploit the information you've uncovered . . .

11. Hi Martin

There are a many ways this can be 'exploited'.
Essentially, this autofill generates a 'straight-line-fit' through the sample cell values that are 'dragged'.
For example, if you entered say, these values in a column:
1.2
2
3.4
6
12
9.7
3.3
5.9
..and then selected them,
then 'auto-fill-drag-down' for say, 6 more cells
then, with the cells still selected, select Charts>xy scatter>finish
..you will see the 'straight-line' fit from the newly auto-filled values.
This can be used in analysis of telemetry data for guided missile tracking for example.
But let's not get into rocket science here.

zeddy

12. This can be used in analysis of telemetry data for guided missile tracking for example.

Missiles are projectiles (like bullets) and generally don't follow straight lines. Their paths tend to be their parabolic so one would need a second-order rather than a first order response. Also exptrapolation of a data set is always risky.

Steve

13. Hi Steve

Imagine a laser beam aimed at a target from a launch site, and imagine that a guided missile is using an angular signal deviation from this 'straight-line' as part of it's guidance control.
(The only time I have problems with extrapolation of data is when I only have a single sample value.)
When data is analysed from such 'straight-line-fits' it can reveal an offset bias in the signal.
Linear interpolation is not applicable for most situations of course.

zeddy

14. Guided with a laser may work, but light is affected by gravity and thus will not be straight, however

Personally, I find linear interpolation to be relatively standard and have been using it since before calculators were common for getting results from tables. Most curves can be approximated by a line if the distance between points is small enough.

But even with hundreds of points, most of the time I find problems with extrapolation. I remember George Box's quote: "All models are wrong, but some are useful". Models tend to be useful in the particular "design space" used to create it, but most times that model is not applicable outside that design space.

Steve

15. Hey guys me thinks it's time to move this to the Rocket Science forum.

16. As Artie Shaw used to say: "Veeeery Interesting...Veeeery Interesting"
RG,
If you were referring to Rowan & Martin's Laugh-In, I believe it was Arte Johnson, as Wolfgang, who quoted the above line.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•