I would have thought you could work that out yourself :-(

Version 98.93

=SUM(IF(G4:BJ4$C$4,$C$4,G4:BJ4)*(MOD(COLUMN(G4:BJ 4)-6,5)=1)*0.09615)

I would put the multiplier in a cell as well and use that (you could always
set different multipliers per employee, which I would have thought you want,
otherwise a guy who is contracted to 169 hours but does 180 only earns
16.24935, whereas a guy contracted to 200 hours but only does 175 gets
16.82625, which hardly seems fair to me).

=SUM(IF(G4:BJ4$C$4,$C$4,G4:BJ4)*(MOD(COLUMN(G4:BJ 4)-6,5)=1)*$D$4)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"r.p.mcmurphy" wrote in message
...
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