What function(s) to use?

[Karen]

I am trying to develop a template for researchers to use to help them develop
project budgets. I am having a problem with one particular formula and am
not clear if I need to use "lookup", "match" or "if", or some combination of
the the three, or something completely different. Here is the problem:

Employees are given raises one time a year (either in July [Faculty change
date] or October [staff change date]) depending on your status.

A grant budget can start in any month of the year and will generally run for
a one-year period.

example: A grant starts in July and ends in June
Staff current salary is 20,000
Staff increase is in October at 5%

Therefore,
Salary in July, August and September is 20,000
Salary starting in October is $21,000
Total salary paid for 12 month period = 20,750 or ($20,000/12*3) +
($21,000/12*9)

I thought about using a nested "If" statement like below.

If(GrantMonth=January and status=Faculty,CurrSal/12*6,
if(GrantMonth=February and status=Faculty,CurrSal/12*5, etc.)

I understand that only 7 if statements can be nested. How can I achieve my
result using a formula?

I don't understand how to use the lookup command.

 

[Pete_UK]

You need to know the number of months before the salary increase, so
somewhere in your sheet you will have the start date for the grant -
assume this is in A1 and is 1st July 2008. Then:

= MONTH(A1)

will give you 7. You know that the salary increases in October (month
10), so number of months before salary increase:

= 10 - MONTH(A1)

The number of months after the salary increase is just 12 minus this
number (9 in this case), or MONTH(A1)+2, so you can just plug these
formulae into what you had written earlier, i.e.:

Total salary paid for 12 month period = (start_salary/12*(10-
MONTH(A1)) + (start_salary*1.05/12*(MONTH(A1)+2))

The 1.05 accounts for your 5% increase, which might come from some
other cell. It would be easier to describe if you had given actual
cell references, but hopefully you can translate this back to your own
sheet.

Hope this helps.

Pete