V-Calculations

V-Calculations - V. Calculations Recommendations...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
Calculations 1 V. Calculations Recommendations Calculations express relationships between values, both known and variable. Avoid using numbers (actual data) in Excel formulas. Instead use cell references that point to cells that hold the data. By keeping each data item in a unique location in the worksheet you can much more easily change an item if necessary and have the change be reflected wherever the item is used throughout the worksheet. In the illustration below a tax rate value is stored in Cell B1. The calculations in Column D all use that tax rate value. Instead of including the actual value of 5% in the formulas, a reference to that cell is made. The formulas in Column D are: =(B4+C4)*$B$1 =(B5+C5)*$B$1 =(B6+C6)*$B$1 Should the tax rate change from 5% to 6% only the value in the tax rate cell, B1, must change. The formulas that use the tax rate refer to the cell, so they need not be changed.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Calculations 2 Copying formulas: relative and absolute addressing You may often want to use the same formula in one or more places in a worksheet. It’s most efficient to build the formula one time and then copy it to the other locations where you need it. If you copy a formula, you must understand Excel’s relative and absolute addressing. For copied formulas Excel uses relative addressing as the default. That is, Excel adjusts the cell references in your source formula when the formula is copied to a new location. In the illustration at left below, the formula in Cell D4 is used to calculate the total sales in January and February for Dept 1: =(B4+C4) . We need the same basic formula in Cells D5 and D6 for Depts 2 and 3. Although we could enter two new formulas, it’s easier to copy the formula in D4 to these locations. Point with the mouse to the “fill box” at the lower right- hand corner of Cell D4 and drag down through Cells D5 and D6 to copy the formula. For each copied formula, Excel automatically adjusted the cell references to refer to the data in the correct row. This is Excel’s default mode when copying formulas. It’s known as relative addressing.
Background image of page 2
Calculations 3 There may be occasions when you want to override Excel’s relative addressing default. In the example below, the formula in Cell E4 (in the Tax column)
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 11

V-Calculations - V. Calculations Recommendations...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online