Right. this is working well.

=(SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4<=(( C4*52)/12))*G4:BJ4)+((C4*52)/12)*SUMPRODUCT((MOD(COLUMN(G4:BJ4),5)=2)*(G4:BJ4( (C4*52)/12)))+BQ4)*0.09615

problem is, i've copied and pasted this formula to the next column but its
not working right there. even though the cells are just one cell off the
pervious one. what i am trying to work out here is sleepin holiday
allowance. it works out exactly the same way as for hours. i am putting in
exactly the same values as for hours but the calculation is returning low.
16.25 down.

=(SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=2)*(H4:BK4<=(( D4*52)/12))*H4:BK4)+((D4*52)/12)*SUMPRODUCT((MOD(COLUMN(H4:BK4),5)=2)*(H4:BK4( (D4*52)/12)))+BR4)*(D4*$E4)/(D4*52)

any ideas? i suppose i could just ask it to add the 16.25....but I'd like
to understand why its doing this!

TIA

steve



"r.p.mcmurphy" wrote in message
...
Yes, what I'm trying to do is get excel to calculate accumulated holiday
entitlement. If and employee works 169 hours a month, then she
accumulates holiday at a rate of .09615 hours per hour worked (working on
an example of a 5 week holiday entitlement)

in cell G4 and every 5th cell there after for 12 occurrences, is the
number of hours worked per month. if during any of these months, they
work over what they are contracted to work (C4) then the value of C4 is
taken as the figure worked for that month.

add these 12 months up, then add on to this figure $BQ4 which is the total
of hours holiday already taken during the year (because holiday is
accumulated during paid holidays also) and multiply the total of these
calculations by .09615

this results in a running total of accumulated hours as we go through the
year.

Hope this helps.

TIA

Steve

i.e. if in month
"Domenic" wrote in message
...
In article ,
"r.p.mcmurphy" wrote:

...there seems to be a problem with the original part now. where i
wanted each 5th cell to be read, if the value of that cell was higher
than
cell C4 then C4's value would take precedent.

Can you elaborate? Maybe provide an example?