Excel Question

[joesxm3]

I have been trying to make some formulas in my spreadsheets and then copy-pasting the cell into several cells in the column, e.g. to add more years to the model.

I have a single cell , say A1 which is the rate of return.

In the sheet I would have a cell B2 asset value and cell C2 new value with a formula like =B2+(B2*A1).

When I copy-paste the cell into cell C3, I end up with =B3+(B3*A2), when what I really want is =B3+(B3*A1).

Is there some way to designate a cell to not "auto-increment" during copy paste?

So far the best I could come up with is to make an entire column for inflation rate and to just repeat the constant number.

Thanks.

Joe

 

[CyclingInvestor]

=b2+(b2*a$1)

 

[obsessed]

Yes there is. Put a "$" symbol in front of the column and row references that you don't want to change. In your case, use $A$1 instead of A1. If you only want the column to remain as "A", enter your formula as $A1.

 

[ocean view]

Adding dollar signs to the cell reference within the formula will make it static rather than dynamic

$A$1 means that as you copy the formula will always refer to cell A1

$A1 will keep the column reference static, but change the row reference

A$1 will keep the row reference static, but change the column.

 

[ocean view]

Btw, if you place your cursor in the formula on the reference that needs the $, function key F4 will cycle through the options

 

[MdaPetralia]

=b2+(b2*a$1)

It should be b2+(b2*$a$1) as the OP is looking for the absolute cell reference to cell A1.

 

[joesxm3]

Wow. I can't begin to thank you all for the help.

You can, no doubt, imagine how much work you have saved me with my previous technique of copying the cell then manually changing the A1 reference.

I guess it was stupid of me to go on so long banging my head on the wall before asking for help.

Thanks again.

 

[clobber]

About now is when you should watch the video You Suck at Excel. Life changing - although he can be a bit tough.

You Suck at Excel

 

[Clarion123]

I have been trying to make some formulas in my spreadsheets and then copy-pasting the cell into several cells in the column, e.g. to add more years to the model.

I have a single cell , say A1 which is the rate of return.

In the sheet I would have a cell B2 asset value and cell C2 new value with a formula like =B2+(B2*A1).

When I copy-paste the cell into cell C3, I end up with =B3+(B3*A2), when what I really want is =B3+(B3*A1).

Is there some way to designate a cell to not "auto-increment" during copy paste?

So far the best I could come up with is to make an entire column for inflation rate and to just repeat the constant number.

Thanks.

Joe

In the original formula B2+(B2*A1),
1. highlight A1
2. Hit the F4 key. The formula will look like this B2+(B2*$A$1)
3. Copy this formula down an your issue should be resolved. Highlighting the A1and hitting F4 anchors the value so when you copy the formula down it will look like
B3+(B3*$A$1)
B4+(B4*$A$1)
B5+(B5*$A$1)........

 

[target2019]

The absolute reference method as mentioned is great.

Another way to control things is to give the cell a name, something like my_first. Then use this name in your formula. When you fill this down, or copy, the name and its reference remain constant.

Even better, put the value in a cell outside of your table, and name it. Then you build the list or table with appropriate reference to the named cell.

As you improve, each technique mentioned becomes useful.

 

[ERD50]

In addition to the other responses, you can give a cell a name (like "Inflation"), and then refer to it by name. That also makes the formula easier to read. (edit - cross posted that with target2019)

Look up how to name cells, I forget the details off hand, but it's easy.

.... So far the best I could come up with is to make an entire column for inflation rate and to just repeat the constant number. ...

I actually like doing it this way. It fits the "KISS" principle. It's direct, easy, no fiddling with the formula and maybe making a mistake. And you can "hide" the cell when you are done if you need the room. And if you enter at the top, and use a formula to point to the one above it, and copy that down with the rest of the formulas, you can just change it in that on cell, and it propagates down to all the others automatically.

-ERD50