Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts

    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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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!
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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
    Attached Files Attached Files

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

    RetiredGeek (2013-05-03)

  10. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thank you for closing out this mystery.

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

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey guys me thinks it's time to move this to the Rocket Science forum.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  16. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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 LastLast

Posting Permissions

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