Personnel Costs - HELP

Hi there,

I have developed a spreadsheet model to calculate various costs for new personnel commencing in a particular project.

Unfortunately I cant figure out the funtions or formulas that I could use to incorporate a person leaving the company/project.

In the attached worksheet, the first tab has all the calculations, the second tab is the input sheet whereby all the FTE (Full-Time Equivalent) details are recorded, e.g. grade, salary, start and end date.

In the calculations tab, the data is pulled from the FTE Input sheet and the salaries are currently divided by 12 to reflect the monthly cost. I was hoping to alter these so that when a person leaves the project, the changes are reflected in the model e.g. if a person commenced in Jan-07, his salary is currently divided by 12 and that figure is used indefinately. If he decides to leave in Jun-07, i would like to be able to apply his costs for the 6 months instead of the current model where there is no stoppage.

It would also be handy to reflect salary changes into teh model (but that's not entirely important at this stage).

Your help will be highly appreciated.

Cheers!

First of all, do you know that if you used Named ranges, you could write all of those formulas in something very much like English?

But, I *think* I understand what you want. Maybe. Here's what I did ...
1. replaced the text in row 4 of the Calculation sheet with real date values and used formatting to get the text to appear the way you want it (similar to what you did in columns E and F on the FTE Input sheet).

2. replaced formula in cell C6 with the following:

=IF(AND(C$4>='FTE Input Sheet'!$E6, C$4<='FTE Input Sheet'!$F6),'FTE Input Sheet'!$D6/12,"")

dragging that formula across row 6, gives (what I think is) the desired result; namely, 16, 667 for Jan-07 through Aug-07 and blanks thereafter.

Hope this helps.

Thanks for your reply mate. Could you please attach the workbook so that I can have a look at the changes.

Cheers!

Dont worry about uploading the workbook mate i've got it all figured out. Thank you very much for ur help ... I really appreciate it.

THANKS HEAPS!!!!!!!