Excel Geek Question

[JayB]

Is there any way to use the autofill function to drag a formula from one cell to the next in a way that keeps one of the cells referenced in the formula constant?

 

In other words, if I have a bunch of cells that I want to subtract the value of cell A1 from, is there a way to use the autofill function so that I get:

 

B2-A1, C2-A1, D2-A1, etc. instead of B2-A1, C2-A2, D2-A3, etc when I try to drag the formula from one cell to another?

[TrogdortheBurninator]

placing dollar signs in front of the column and row locks that cell. you can also cycle through locking iterations by pressing F4 with the cursor next to the prospective locked cell in the formula.

[gertlush]

If you put a '$' sign in front of the A or the 1 I believe it holds it constant, i.e. $A1 just keeps the A the same but the 1 will change and vice versa.

[chucK]

In the formula put a "$" before the value you want to remain static. E.g. if you want to keep the row static A$1, column static $A1, both static $A$1.

 

whoa!! geeks popping out of the woodwork. Like whack a mole!

Edited November 21, 2007 by chucK

[TrogdortheBurninator]

i.e. =B2-$A$1

[iciclespyder]

try B2-A$1

[jon]

Put $ in front like $B$1 to keep it constant in an autofill formula.

[jon]

Holy shit I know where to go now if I'm stumped in Excel.

[JayB]

Dear God - that was fast! Never had to do that before. Definitely beats typing A1 a gazillion times. Many thanks for the help.

[pope]

Here's another way. Click in A1, then "insert","name" and "define". In the "names in the window" line type "k" and click OK. Back in A1, type the value that you want. Enter your data in column B, then in C1 type " = B1-k " (no quotes). Then drag the corner of B1 down.

 

I wish I knew a lot more about the program. It seems there's nothing it can't do.

[selkirk]

Screw Excel, get Matlab

[archenemy]

Screw MatLab. Get Astroids.

[builder206]

x^2/3 + y^2/3 = 1?

[archenemy]

I feel like such a square.

Thanks E+6

[ClimbingPanther]

delete selkirk.m

[Off_White]

Screw MatLab. Get Astroids.

 

[Dechristo]

should soothe his burning, itching gums and reduce the swelling of his lips.

 

Perhaps, he's aghast to read that it's prescribed to be applied to his anus first.

[Bill_Simpkins]

Screw Excel, get Matlab

Screw Matlab, code it yourself.

[i_like_sun]

The new version of Office is a bunch of crap. I used to know how to use Excel super well, now I guess only Bill can use it.

[crackers]

afaik, ctrl-f3 calls the "name" definition window, or if the appropriate cell is active, you can type in a name in the left side of the formula bar. If you want to make a cell absolute, you should be able to just hit f4 when the cell is active. First f4, $a$1, second, $a1, third a$1 and fourth a1. That's off the top of my head.

 

You should name things the right name. Do not use something like average, or mean, or any other function name or single letter that might create confusion.

 

MatLab is good for some stuff, STATA is good for others, coding is good for others, and excel is great for a simple GUI that people who shouldn't be allowed near those other systems or the data.

 

 

[crackers]

btw, the rnd function is total crap. if you need a random number generator, don't use rnd()...

[chucK]

New question

 

How do I format the top row (or rows) to be a header so that when I scroll down the top row stays put (so I can keep seeing the variable labels)?

[jon]

Well on the mac at the top of the slider bar there is a blue button you can grab and slide down.

 

That's childs play!

[TrogdortheBurninator]

click on the first column of the row below those you want to freeze. go to Window: Free Panes

[TrogdortheBurninator]

btw, the rnd function is total crap. if you need a random number generator, don't use rnd()...

 

when has the RAND() function failed you? Excel 2003 and newer has a revised random number generator algorithm. Even the old algorithm was sufficient for probably 99.9%+ of all users.