To calculate a cost of living (COLA) adjustment in Excel, you can use a simple formula that multiplies the base times the adjustment percentage, then adds the result to the base. In the example shown, the formula in C6, copied down, is:
where cola is the named range F6, and contains the adjustment as a percentage.
The goal in this example to calculate a cost of living (COLA) adjustment for each of the eleven years shown, starting on the second year. The actual adjustment percentage is entered in cell F6, which is the named range cola. Each year, the adjustment should be applied to the previous base amount in column C, starting with the base amount in cell C5.
The formula in cell C6, copied down is:
With 30,000 in C5 as shown, the formula is solved like this:
As the formula is copied down the table, the relative reference C5 changes at each row, while the named rangecola (F6) behaves like an absolute reference and does not change. The result on each new row is the previous year's base plus the adjustment.
To calculate to the total of all adjustments, the formula in F7 is:
In the example, the active cell contains this formula: = C6 * ( 1 + D6 ) In this case, Excel first calculates the result of 1 + the value in D6 (.2) to get 1.2 which it then multiplies times the value in C6 (70) to get a final result of 84: =70 * (1...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.