Im trying to set up a holiday accrual worksheet for my employees, so that i
can tell at a glance how much holiday they have left.

basicaly, employees acrue holiday entitlement for every hour they work upto
their contracted hours per calendar month.

after each employees name, i have a box which shows what their contracted
hours are. how do i get excel to refer to this figure and use it as the
maximum when determining the amount of hours worked in any particlular
month?

Cheers!

steve

Perhaps something like

=MIN(M1,SUM(A1:A30)

where M1 is the contracted hours, A1:A30 holds the hours worked

--

HTH

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


"R.P.McMurphy" wrote in message
...
Im trying to set up a holiday accrual worksheet for my employees, so that
i
can tell at a glance how much holiday they have left.

basicaly, employees acrue holiday entitlement for every hour they work
upto
their contracted hours per calendar month.

after each employees name, i have a box which shows what their contracted
hours are. how do i get excel to refer to this figure and use it as the
maximum when determining the amount of hours worked in any particlular
month?

Cheers!

steve

Ok, Now I've got -

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE 4,BJ4))

i take it this will add up G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE4,BJ4 as
long as each of these doesnt go over C4, and if any do, then they are
counted as the value in C4?

If it does thats great! Next problem is how do i divide the answer by
0.09615 in the same formula?

Hehe! Sorry about this. I'm a newbe to Excell!

steve



Perhaps something like

=MIN(M1,SUM(A1:A30)

where M1 is the contracted hours, A1:A30 holds the hours worked

--

HTH

Im trying to set up a holiday accrual worksheet for my employees, so that
i
can tell at a glance how much holiday they have left.

basicaly, employees acrue holiday entitlement for every hour they work
upto
their contracted hours per calendar month.

after each employees name, i have a box which shows what their contracted
hours are. how do i get excel to refer to this figure and use it as the
maximum when determining the amount of hours worked in any particlular
month?

Cheers!

steve

"R.P.McMurphy" wrote in message
...
Ok, Now I've got -

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE 4,BJ4))

i take it this will add up G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE4,BJ4 as
long as each of these doesnt go over C4, and if any do, then they are
counted as the value in C4?

As long as the sum of those cells does not exceed C4.


If it does thats great! Next problem is how do i divide the answer by
0.09615 in the same formula?

Ooh, watch carefully, it's tricky <vbg

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE 4,BJ4))/0.09615

but are you sure you shouldn't multiply? I assume they get 0.09615 hours per
hour worked so that would be multiply

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE 4,BJ4))*0.09615

"Bob Phillips" wrote in message
...


"R.P.McMurphy" wrote in message
...
Ok, Now I've got -

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE 4,BJ4))

i take it this will add up G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE4,BJ4 as
long as each of these doesnt go over C4, and if any do, then they are
counted as the value in C4?

As long as the sum of those cells does not exceed C4.


If it does thats great! Next problem is how do i divide the answer by
0.09615 in the same formula?

Ooh, watch carefully, it's tricky <vbg

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE 4,BJ4))/0.09615

but are you sure you shouldn't multiply? I assume they get 0.09615 hours
per
hour worked so that would be multiply

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE 4,BJ4))*0.09615


Ah yes...you spotted the deliberate mistake! ;-)

so, just going back to the first calculation... if C4(the contracted hours)
= 39 and G4(actual hours worked) was 41 and L4(actual hours worked) was 33
(and all the others were zero) the outcome would be 39+33?

ta

steve

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

Ah yes...you spotted the deliberate mistake! ;-)

so, just going back to the first calculation... if C4(the contracted
hours)
= 39 and G4(actual hours worked) was 41 and L4(actual hours worked) was 33
(and all the others were zero) the outcome would be 39+33?

No, the formula is

=MIN(contracted_hours, worked_hours)

In this case, worked_hours is 41 (G4) and 33 (L4), or 74, contracted hours
is 39, so the result is the minimum value of 29 and 74, i.e. 39.

As I understand it, a worker gets .09615 hours per hour worked up to the
contracted total for the month. So the formula calculates then, then
multiply by .09615 and you have it

=MIN(contracted_hours, worked_hours)*.09615

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

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 in message
...
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

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

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

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

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

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

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

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

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

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

Oh by the way, only one person so far has realised where the name
R.P.McMurphy came from.

I'll give you a clue. He was a character in a film. ;-)

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

Yeah, of course I recognised Randle Patrick McMurphy from OFOTCN first time
I saw it, read the book, seen the film several times. Just thought it was
one of those co-incidences. But I still don't get the connection.

Bob

"r.p.mcmurphy" wrote in message
...
Oh by the way, only one person so far has realised where the name
R.P.McMurphy came from.

I'll give you a clue. He was a character in a film. ;-)

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

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

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

I'm a psychie nurse. I could call myself Nurse Ratchet, but that would be
misleading wouldn't it?

:-)

steve

"Bob Phillips" wrote in message
...
Yeah, of course I recognised Randle Patrick McMurphy from OFOTCN first
time
I saw it, read the book, seen the film several times. Just thought it was
one of those co-incidences. But I still don't get the connection.

Bob

"r.p.mcmurphy" wrote in message
...
Oh by the way, only one person so far has realised where the name
R.P.McMurphy came from.

I'll give you a clue. He was a character in a film. ;-)

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

But RP was a victim of such nurses. You have to call yourself Nurse Ratched.

Did you know that Philip Roth appeared in that film?

--

HTH

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


"r.p.mcmurphy" wrote in message
...
I'm a psychie nurse. I could call myself Nurse Ratchet, but that would be
misleading wouldn't it?

:-)

steve

"Bob Phillips" wrote in message
...
Yeah, of course I recognised Randle Patrick McMurphy from OFOTCN first
time
I saw it, read the book, seen the film several times. Just thought it
was
one of those co-incidences. But I still don't get the connection.

Bob

"r.p.mcmurphy" wrote in message
...
Oh by the way, only one person so far has realised where the name
R.P.McMurphy came from.

I'll give you a clue. He was a character in a film. ;-)

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

As Woolsey or something like that. cant remeber what he looked like now.
time i saw it again!

steve

"Bob Phillips" wrote in message
...
But RP was a victim of such nurses. You have to call yourself Nurse
Ratched.

Did you know that Philip Roth appeared in that film?

--

HTH

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


"r.p.mcmurphy" wrote in message
...
I'm a psychie nurse. I could call myself Nurse Ratchet, but that would
be
misleading wouldn't it?

:-)

steve

"Bob Phillips" wrote in message
...
Yeah, of course I recognised Randle Patrick McMurphy from OFOTCN first
time
I saw it, read the book, seen the film several times. Just thought it
was
one of those co-incidences. But I still don't get the connection.

Bob

"r.p.mcmurphy" wrote in message
...
Oh by the way, only one person so far has realised where the name
R.P.McMurphy came from.

I'll give you a clue. He was a character in a film. ;-)

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