Home |
Search |
Today's Posts |
#1
|
|||
|
|||
need help with Formula
Hi
i have created a staff in post excel document but i am now stuck on a formula a full time member of staff in our office works 37 hours per week which equates to 1.00. But we also have staff that work part year and term time i need the formula to work out to the decimal place i can work this out manually but there are quite a few staff in the office and continually changes manual way to do this would be: TO WORK OUT PART TIME HOURS 26 hours (staff members part time hours) divided by 37 (the full full time weekly hours) the SIP (staff in post) would be 0.70 BUT TO WORK OUT PART YEAR AND TERM TIME The calculation would be someone working 30 hours per week for 37 weeks per year would be (30 [staffs part time hours]/37 [full time equivalent hours]) x (37 [weeks they will work in the year/52 [full working year]) = 0.58 (rounded to nearest decimal place i need a formula to work this out for me in cells H5 to H53 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with Formula
Hi
Not sure where or why you came up with your formula: If you have 30 then divided it by 37, then multiply by 37 again, you end up back at your starting point of 30. So it would have been simpler to have just done 30 divided by 52 which gives you 0.5769 or in your case 0.58.. So, let us assume the following: Column A = Hours Column B = Weeks Column H = Decimal Pay Rate/Structure In H5 ( formatted to 2 decimal places ) you would simply do: =IF($A2="","",($A2/$B2)). You could conversely approach it from another angle being the overall full time hours worked v. the overall part time / casual hours, BTW equates to the same, e.g. Part Time: Hours 30 x Weeks 37 = Total Hours 1110 Full Time: Hours 37 x Weeks 52 = Total Hours 1924 1110/1924 = 0.5769 In as much as I am not privy to why you would need it this way, my own dealing with this type of structure in the past has always been focused on the decimal factoring of full time hours not tenure. HTH Mick. |
#3
|
|||
|
|||
Quote:
i managed to find the formula on the work intranet =(I5*J5)/52/37 __________________________________________________ ______________ But i need help with something that looks simple but i cant for the life of me work it out In cells E10 i want to input a date e.g 01/02/12 i want the worksheet to automatically work out what target i have hit in cell G10 my targets are 13 weeks 26 week 39 week 52 week 52+ i want the cell G10 to populate the content into one of the 5 targets based on the date i put in so if i input 01/02/12 cell G10 should automatically have text entered "39 Week" i also want the formula to work in all cells E10 and G10 onwards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula autofill to cells below – formula arguments are cell areas in different columns | Excel Discussion (Misc queries) | |||
Build formula using field values as text in the formula referencing another workbook | Links and Linking in Excel | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |