Not quite there...definitely getting there though! The other thing i should
add is that the contracted hours differs for each employee! hence the need
for excel to refer to column C4 for the contracted hours for that employee.
hehe...hope its keeping you busy this?
ta
steve
"Bob Phillips" wrote
Users, pain in the neck!
Version 97.23
=SUM(IF(G4:BJ4169,169,G4:BJ4)*(MOD(COLUMN(G4:BJ4)-6,5)=1)*0.09615)
This is an array formula, so commit with Ctrl-Shift-Enter, you will see
curly brackets around it in the formula bar, inserted by Excel
Bob
"r.p.mcmurphy" wrote
Ok i may have not explaind this well enough. this spread sheet should
add
up 12 figures, one for every month worked and multiply it by .09615 to
come
up with the amount of holiday accumulated. however if an employee worked
over a certain amount of hours(the contracted amount) the contracted
amount
takes precedance.
ie, contracted hours a month is 169, totals for each month worked were,
169,
166, 150, 180, 169, 155 ,0,0,0,0,0,0 (as the employee has only worked
half
way through the year so far in this example) excel would interprate these
as
169, 166, 150, _169_, 169, 155, 0, 0, 0, 0, 0, 0. add them up and
multiply
them by 0.09615.
Sorry for any confusion!
Many thanks for your help, its realy appreciated!
steve
|