Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function(s) to use?
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. -- Karen |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function(s) to use?
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 On Mar 11, 8:26*pm, Karen wrote: 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. -- Karen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |