Well I'm in the UK as well, sunny south coast.

Is the nearest quarter hour serious, or you just rubbing me up?

Bob

Have one on the boys for tomorrow.

"R.P.McMurphy" wrote in message
...
iTS LOOKING GREAT bOB! oops...sorry.

im gonna try it out on real world figures next. gonna have to be tommorow
now though...its 21.30hrs here in UK, so i'm off for a beer! Have one on
me
mate! If i'm ever in your part of the world i'll buy you one myself!

oooo...one other thing...hehehe! how do i get it to round the answer to
the
nearest quater?

Thanks again...hehe!

steve

"Bob Phillips" wrote in message
...
Steve,

A question for you!. How do you get Steve from R P McMurphy?

The original formula did a calculation based upon the column number, so
just
copying across won't work as it is. If we change my original formula to


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

and you build the other one as you did, it should work now.

Post back how you get on.

--

HTH

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


"R.P.McMurphy" wrote in message
...
hI Bob, just another quickie! its working great...but we also do
sleeping
shifts which we calculate seperatly. the number worked is place in the
next
box along. so ive used the same array in the next field but changed C4
to
D4 in both instances where they appear in the array. excel has changed
all
the other variables accordingly.

however its not calculating right. any reason that might be? should i
be
changing anything else in the array?

Cheers for your assistance!

steve
"Bob Phillips" wrote in message
...
Only teasing. That is a big leap from lists !

Bob


"R.P.McMurphy" wrote in message
...

Unfortunatly i dont work with excel and have never used it for more
than
making lists and adding them up.
Thanks for your help though. :-)

steve

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