Solved

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

Posted on 2011-02-10
275 Views
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
Question by:BHBMHT
• 2

LVL 33

Accepted Solution

jppinto earned 125 total points
just change the C33 on your formula to \$C\$33

jppinto
0

LVL 39

Expert Comment

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

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

That is EXACTLY what I was needing. Thanks!
0

## Featured Post

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…