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

Also, a spill function calculation will only spill into empty space. You can not have two things in the same cell - how confusing would that be. So, even when there is not a circular error, a spill error will occur when the area to be spilled into has even one cell that is already occupied. This is very often a blessing so that something needed does not get overwritten, of if the spill just surrounded that value or skipped calculating for that cell, or whatever other scenario you would like it to do, this would create all kinds of problems for computations. Any calculation that needs to look at a cell that was expected to have the spill value but contained its original value instead would not produce the expected value.

So, thanks to MS for thinking it through quite well.

Answer 2

That is a waaaay-cool chart!

If you convert your table to an Excel Table

You can add a column of formulas that match your data set automatically by just typing the formula in the second row below the header in the first blank column to the right your table - say, F6 - using =F5+1 - that formula will be propagated down the column to match:

The formula will return errors at first, until you add your seed number to F5:

Then you can shift-click-drag that column to wherever you want it, like at the left of the table:

Answer 3

What is it that you are trying to do? Create an index in a column? Are you matching against other data?

Answer 4

This is the chart I'm creating: this is a scattergraph, everything you see is either a line or a dot, including the globe, the tracking solar wings on the satellite, etc. It also generates power and data budgets, groundstation access, etc.

Answer 5

I run a propagator, different starting conditions develop over time (each row a timestep)

So each next row depends on the previous ones, and there are links between the columns too of course