Solved

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

Posted on 2011-02-10
4
309 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel If Statement 3 27
Power Query Grouping By 2 19
Populating a Cell with specific Characters 13 16
How to always round a decimal up 5 21
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will show you how to use shortcut menus in the Access run-time environment.
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…
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…

733 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