Solved

Is there a way to make a static variable in a division formula in Excel 2007

Posted on 2011-02-10
4
296 Views
Last Modified: 2012-05-11
I am trying divide multiple cell by the same cell, but when I go to drag down my autofill box, it changes the 2nd value (which I want to remain static).

For example, I want
=B1/C33
=B2/C33
=B3/C33
...all the way to B30

When I copy the first two cell and try to drag it down (so it populates the rest), it changes the C33 value as well. Is there a way to make it so it stays static?

2nd part of the question is when I do a sort, it changes the C33 part again, thus messing up the division again. So if there is a way to have it stay static (I would want the first variable to change becuase it would be on a new line once sorted)?
0
Comment
Question by:BHBMHT
  • 2
4 Comments
 
LVL 33

Accepted Solution

by:
jppinto earned 125 total points
ID: 34865547
just change the C33 on your formula to $C$33

jppinto
0
 
LVL 39

Expert Comment

by:nutsch
ID: 34865550
You can use absolute addressing instead of relative addressing

if you copy C33, column and row info will shift, by putting $ signs in front of column, row or both, you fix that dimension.

In your case, you would use
=b1/$C$33

and could copy it wherever you want. The B1 part would shift, but C33 is fixed by the $ signs.

Thomas
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34865571
Using the $ simbol will fix the coordinate of the row or the column.

Example:

C$33 - will fix the row number, no matter where you copy the formula, it will mantain the reference to row 33
$C33 - will fix the column letter.
$C$33 - will fix the row number and column letter

jppinto
0
 

Author Closing Comment

by:BHBMHT
ID: 34865612
That is EXACTLY what I was needing. Thanks!
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question