Solved

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

Posted on 2011-02-10
4
315 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

717 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