Simple question about excel.

[LiF3tim3]

Hi there. I'm keeping a simple score for my game guild.

I need to know the difference of =A1-B1, =A2-B2 =A3-B3 etc. When I add a new column it goes to =B1-C1, =B2-C2, etc. How do I keep the formula to stay on the new column A?

I've attempted to use the $ symbol but it doesn't seem to work. Spent alot of time using the internet trying to figure this out to no avail.

Thanks for the help.

 

[JoeMo]

If you add a new column before col A or col B, Excel will keep track of the cells that contain the values you initially wanted the difference between. That will happen whether or not you anchor the columns with $ signs. For most people that's a nice feature.

Say initially I have 10 in A1 and 5 in B1, then =A1-B1 returns 5. If I add a column before column A, A1 now is empty, B1 contains 10 and C1 5. So, =B1-C1 (or $B1-$C1) still returns 5, while =A1-B1 returns -10, not the answer most folks are looking for.

 

[LiF3tim3]

Yep I understand that. however I'm adding a new column A to add in new scores and don't need to know the value of B1-C2 anymore.

So I'm looking for the thing that most people aren't. Thanks for the quick response.

 

[AhoyNC]

Try using INDIRECT function.

=INDIRECT("A"&ROW($A1))-B1

Copy down as needed.

 

[LiF3tim3]

Thanks very much JoeMo and AhoyNC!! Greatly appreciate it! I really mean it. Spent alot of time trying to figure this out and seeing the solution I'll probably never would have.