?
Solved

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

Posted on 2011-02-10
4
Medium Priority
?
323 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 500 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

807 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