1 / 6

Absolute vs. Relative references

Absolute vs. Relative references. An introduction. #DIV/0! means Excel is trying to tell you that you have asked it to divide by 0 -- an operation for which there is no answer. Why is this happening?. Copying formulas does not always work.

hadassah-hancock
Download Presentation

Absolute vs. Relative references

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Absolute vs. Relative references An introduction

  2. #DIV/0! means Excel is trying to tell you that you have asked it to divide by 0 -- an operation for which there is no answer. Why is this happening? Copying formulas does not always work This formula calculates first-quarter sales as a percentage of total year sales. Not surprisingly, it divides the number in cell B4 ($1,000) by the number in cell F4 ($4,100). However, when you pull the extension handle to copy the formula for Quarters 2,3, and 4, you don’t get the results you want. Press the Page Down key several times to see what happens.

  3. Formula references To understand why this may be so, consider what the above formula is really saying. To Excel, B4/F4 actually translates as “when I’m sitting in cell B5, divide the number that is one row above me by the number that is four columns to my right and one row up.” In other words, the formula as written tells Excel to make a calculation relative to the cursor’s current position. Remember: the cursor always tells to Excel its current location (note B5 in the name box above). What you need to do for the formula to copy correctly is to tell Excel always to use the $4,100 (i.e., the contents of cell F4) -- no matter where you copy the formula. To do this, you need to make the reference to cell F4 an absolute reference rather than a relative reference. Use Page Down to see how to do this.

  4. Use the F4 key for absolute references The screen above shows the formula corrected so that it will give the desired results after it is copied. To convert a relative reference to an absolute reference while building a formula, press the F4 key immediately after clicking on the desired location with the cursor (in this case, the immediately after selecting the cell that has $4,100 in it). The formula changes to show a $ in front of both the column and the row for that location. You will notice that if you continue to press the F4 key, the command toggles to set the column, then the row, then both as absolute references. Trivia: Excel uses the $ to indicate absolute references because in the early days of spreadsheets PC keyboards had so few keys that software designers had to find multiple uses for each symbol. The company that first did this now out of business, but the $ stuck.

  5. Use the F4 key for absolute references This screen shows the result after you copy a formula that is correctly referenced. Use the Page Down key to walk through the steps. When you’re done, use Page Down to see a sequence that shows setting the absolute reference.

  6. The F4 key has been pressed to right here to lock this location as an absolute reference. This time, all the copied formulas refer correctly to the $4,100 because that location has been set in the original formula as an absolute reference. You can check the results by looking at the copied formulas. They all refer to $F$4. Setting an absolute reference

More Related