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

Hi, and thanks for taking the time. The fib seems more like a sequence, where the starting conditions are inside the formula, possibly referenced from a different location. I am running a propagator, where each next value is a combination of the previous value plus a couple of other values from neighboring columns. That way I calculate position, velocity, and acceleration, It works just fine if I simply pull down the formulas to copy them, but it is many thousands of lines, making for big spreadsheet files. I realize that strictly speaking the array covers the initial cell, but at the same time, no individual cell's result is dependent on any circular calculation. Cell B8 references the information in cell B7 (and in the actual sheet also say A7, D7, E7, etc). Similarly B9 references the value in cell B8, etc. The propagator starts with a few starting parameters, hence the extension of the array to include that first cell above the formula.

Without this functionality there's so little usability in array formulas, that excel might as well just go back the old {} notation for those people who like the slowness of that implementation, and stop hijacking existing worksheets with erroneous spill errors.

Answer 2

> ... , that's where Excel is mistaken.

EricCollijns5 is correct. You asked:

> ... I want to use: b6 =B5:B20+1

B6 is within B5:B20. Excel (and Eric) are correct that this is a circular error.

I don't think you are explaining yourself correctly. Excel can refer to previous cells dynamically, but it has to be done differenlty.

Answer 3

Well, that's where Excel is mistaken.

Cell B6 references cell B5, B7 refers to B6, etc, it's just combined in an array, but no circular calculation here.

This is a significant, and in my view erroneous, restriction for the use of spilled formulas. For the painful way they were forced upon the users, the least MS could have done is make them actually work correctly.

Answer 4

A cell can not use itself in a formula. For example, cell B6 can not contain a formula such as =B6+1. This is why you are getting an error. Your formula, =B5:B20+1 in cell B6 is doing just that, trying to add 1 to itself, just with a spill function. You will need to put the formula in a different a cell that doesn't let the results spill into itself.