Excel Idiot Needs Formula Help!!

[Donna123]

I posted earlier as a Novice, but have since reduced myself to idiot
status. Working on our budget for 2006, which up to now is largely a
manual process. We of course want to use the budget as a predictor
going forward and as an actual looking backward. So, whenever
something happens, we have to manually input the data. So, I'm looking
for help with setting up the formula. Here's what I've got:

Columns A-E contain misc stull not truly relevant to the formula.
Column H - actual base pay for each employee as of Dec 31.
Column I - anticipated increase for the year (given on their
anniversary date and set at 3%)
Column J - Date of last increase (should all be 2005)
Column K - Increase date (to be filled when increase occurs)
Column L - Actual Increase amouont
Column M - Monthly Base (should be Column H/12 until increase date,
then it needs to be reflective of Column L).
Column N - Y Jan, Feb, Mar, April, etc

Example:
John Doe's anniversary is March 1. I have the following information
listed for him:
Column H - $35000
Column I - $36050
Column J - 03/01/05
Column K left blank until March
Column L left blank until Mar
Column M - =H/12 for Jan and Feb (columns N and O)

Now it is Mar 1 and John receives an increase so I'll put the date in
Column K and the actual amount in Column L, let's say it was 2% so that
would be $35700. I want the spreadsheet to automatically calculate the
information for the remaining months of Mar - Dec. How would I do
that?

 

[peto]

Add an extra column at N called % increase & input the % increase

then in your month columns don' call them jan feb etc call them 31/1/05 ,
28/2/05 etc
then put this formula in 31/1/05 column & drag across to 31/12/05 column

IF(Q1>$K$3,($H$3+($H$3*$N$3))/12,$H$3/12)


the columns should read like this

H I J K L
M N O - AA
Act base Anticip Date of Increase Actual Month %
Pay Increase last incr date Incr amt Base
increase 31/3/05 etc

35000 35700 3/01/05 01/03/05 58.33 2916.67 2%


Note the absolute references in the formula ( $ )