I am trying to perform an operation in a cell based on the value of the cell above it. Doing this in the "normal" Excel way is easy enough. However, I am playing with the array functionality and would like to do this with arrays. A simple example of this is compounding
It seems like Columns A and B should be identical because there isn't actually a circular reference. Any way to work around this?
please check if the following formula if helpful to you. In cell B2:
=POWER(1.1,SEQUENCE(10))
You could also try =POWER(1.1,SEQUENCE(10)-1) in cell B1 to replace the Number 1.
Any misunderstandings, please let me know.
Not precisely as you seem to be showing in the picture.
You don't actually say what makes you ask the question, but looking at your picture, the obvious problem you could be seeing is Excel telling you you've created a circular reference loop or giving you "0" in B2 and nothing below. The first is not directly likely, but searching the internet might've told you the entry of "0" in B2 and nothing below it means circular references.
And since you HAVE done that, I guess they're right, eh?
You can't make the approach work. Even if you put a "1" in cell B1 and then a formula in B2 like =B1:B9*1.1 thinking that then B2 would be referring to B1, and so on, so you wouldn't be making circular references, it would still fail because that isn't how it works.
What you need to do is give Excel some help. You are not literally creating circular references "and that's that"... You are only setting things up so that Excel cannot be sure just what you really do want. It has to consider the result you get (but never get to see) AND the idea that you want to do what you seem to want to do. Because it can't figure out which you want, and they are different, it stops and either tells you there are circular references or it just puts a "0" in B2 and does nothing more.
A very slightly different typing of your formula will clear all that up for Excel. Put your range in parentheses: (B1:B8) as below, rather than not in parentheses:
=(B1:B9)*1.1
Enter this in B2 and it will SPILL into B2:B10. So, slightly different, but that slight difference makes ALL the difference. Now Excel knows EXACTLY what you want, there is no ambiguity making it dither about what to show until the calculation engine moves on leaving nothing behind.
Remember this detail, the use of parentheses when nothing about the formula seems to require them. It is insane how many times it makes all the difference. Remember it and when you try something, it doesn't work, and you verify all seems to be just how a help site talked about it being, you might remember this and try it. So many times it will work. Any time an expression SHOULD evaluate cleanly but does not, whether it is a simple range like now or a full-blown 3-4 inches of formula, putting logical chunks in parentheses will often change an error return into a real value, or take a SINGLE returned value and replace it with a full SPILL array as desired.
