Nope, that dosnt work. this is what is working atm.

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

how do i get it round to the nearest quater?

also, can you explain this formula in normal terms? thats the only way i'm
gonna learn how to do this myself...and then i wont have to pick your
brains! Excel help dosnt seem very useful!

steve
"Bob Phillips" wrote in message
...
Half hour or quarter hour?

=ROUND(SUM(IF(G4:BJ4$C$4,$C$4,G4:BJ4)*(MOD(COLUMN (G4:BJ4)-6,5)=COLUMN(A1))*
0.09615)/15,0)*15

for quarter hour.

I sit awaiting your next addition :-(


"r.p.mcmurphy" wrote in message
...
Lol...nope...I'm serious! i have tried formatting the cells but that
only
rounds to tenths. we pay to the nearest half hour.

i could do a course on excel, but since i rarely use it, I'll forget it
by
the next time i use it!

lol;-)

steve

"Bob Phillips" wrote in message
...
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