Array formula error

Question

I want to use: b6 =B5:B20+1 (the actual formula is much longer, thousands of rows etc, but this is the gist, and gives the same error)

If I use b5 =20

                    b6    =b5+1

                    b7    =b6+1

                      etc

it all works just fine, and it is exactly the same as the array formula I'm trying to spill into the cells below.

This is not a circular reference, but Excel mistakenly thinks it is and refuses to spill the array. How to fix?

Thanks,

Maarten

Answer 1

Thanks for your considered reply. I knew that double-clicking the black square is the same as dragging down, but as it has the same effect, I called it dragging, instead of describing is as the double click on the little black square in the right-hand-bottom corner.

I also know that there are certain functions that might be easier/quicker to code with the array formulas, I also know that if I want say the max of each row of a 3x1000 array individually, that spill has trouble. And other issues. You might not be aware of a recent fix to Excel that I caused: if you had a spreadsheet with check-boxes that changed values on another sheet, and that spreadsheet was located in Sharepoint, it would immediately crash excel into oblivion the moment you touched the check box. Not freeze or so, just disappear entirely in an instant. MS agreed that it was a bug, and fixed it earlier this year.

This non-spilling IMHO is also a bug, with its coding only allowing certain limited functionality, and confusing itself. If the drag down (sorry, double click) works, the spill should too. That's my simple rule. You could have had the spill-type behavior before to make your life easy with the {} version of it, although that version was achingly slow. I had hoped that MS would have made it more useful before forcing it upon everyone, and forcing the @ usage to try stop it from spilling formulas that should have never spilled in the first place.

I really like Excel, but I would also really like it to do what I tell it to do, and not try thinking for itself (e.g. selecting data for a scatter chart fails more often than that it works, it's much worse than older Excel versions, but that would be another thread)

Did you see the charts I have in my sheets? I copied an example earlier in this thread. They also create animations with the click of a button, spit out detailed analysis reports, etc. Now for that Spill to be made to work.

Answer 2

MS did think through spill functions before rolling them out and they work perfectly! I am a data analyst and spill capabilities serve amazingly to streamline so many tedious calculations. I use this capability every single day! We now have tasks that used to take a week for one worker to do that are now done in just a few minutes due to this wonderful capability! Also, think about it, MAX is not a spill function, but if you have the numbers 1-6 in cells A1:A6, then in cell A4 you delete its number and type =MAX(A1:A6), Excel will give an error just as it did when MAX was first introduced. Excel functions have never been able to compute with the cell in which a formula resides. This has always been a circular error and will always continue to be one.

Also, you need to move past "dragging down". Once you type a formula, look for the small square in the lower right corner of the cell (see the selected cell in this image). After typing a formula, double-click on that little square and it will fill the formula down for as many contiguous cells in the immediately adjacent column that have data in it. In the sheet this screenshot was taken from, I have 1,000,000 cells of data in C:C. It would take a long time to drag down to all of those. Double clicking on the small box in the bottom right corner did the fill down in about 2 seconds - on a 6 year old Surface that is really lame on its speed these days.

For anyone who wants to get more out of Excel, and if they have Linked In Learning, I suggest going through an Excel lesson series. I came across the one by Dennis Taylor a few years back and it took my Excel ability to a whole new level in so many ways.

Answer 3

Well, dragging down is what I do now (and have been doing for decades), I had hoped that MS would not introduce something this dramatic without actually providing full usability, and not limited to the simplest of tasks.

Currently I drag down about 9000 rows, would like to make that 90,000 or more rows, but the spreadsheet is 14 MB now, and it would become unwieldy at 140 MB, never mind 1.4 GB. I already make sure that any non-used rows change to an error, which takes much less space to save compared to all values, but it only does so much.

I still think that as there is NO circular calculation, it should just work, Sounds like MS wanted to push it out before thinking through what people might need it for.