1

Yesterday, I needed to make a list of multiples of 68, so I wrote the formulae =1*68, =2*68 and =3*68 and I dragged down. To my surprise, Excel just repeated =1*68, =2*68 and =3*68.

However, if I calculated those number myself (68, 136, 204) and start dragging down, the sequence gets continued (272, 304, 408).

Is there a rule for this? Is this a (known) bug and (when) can we expect a correction? ...? I'm working with Excel-365.

For your information, hereby a screenshot:

screenshot

P.s. the question reviewer mentions this to be subjective, but there is nothing subjective about this easily reproducible issue.

Dominique
  • 1,993
  • 9
  • 30
  • 65
  • It will not adjust the hard coded numbers in the formula. It looks for patterns in user defined values as you drag but will not parse hard coded values in a formula. Instead use `=ROW($ZZ1)*68` and drag down. – Scott Craner Apr 27 '23 at 14:10
  • or `=SEQUENCE(7)*68` and it will spill – Scott Craner Apr 27 '23 at 14:17

1 Answers1

1

Excel tries to be helpful, but its options are limited.

What you're seeing is called Auto Fill : If you select a series of numbers and drag the Fill Handle, then Excel will try and find a rule for extending the series.

If it cannot find a rule (and the options are very limited), it will just repeat the entered values as many times as it can.

Excel can find a rule for an arithmetic series like "68, 136, 204", but it doesn't have the intelligence to understand that in the formula "=1*68" it needs to increment the 1, so it just repeats the formulas.

Perhaps the new Artificial Intelligence that Microsoft intends incorporating into Office 365 will do better in the future, but for the moment that's all that it can do.

So this is not a bug, just a very limited feature.

harrymc
  • 455,459
  • 31
  • 526
  • 924