How do i do this?

[R.P.McMurphy]

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

 

[Bob Phillips]

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]

Ok, Now I've got -

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE4,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

 

[Bob Phillips]

Ok, Now I've got -

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE4,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,BE4,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,BE4,BJ4))*0.09615

 

[R.P.McMurphy]

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



Ooh, watch carefully, it's tricky <vbg>

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE4,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,BE4,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

 

[Bob Phillips]

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

 

[r.p.mcmurphy]

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

 

[Bob Phillips]

Users, pain in the neck!

Version 97.23

=SUM(IF(G4:BJ4>169,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]

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]

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:BJ4)-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:BJ4)-6,5)=1)*$D$4)


--

HTH

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

 

[R.P.McMurphy]

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

 

[Bob Phillips]

Only teasing. That is a big leap from lists !

Bob

 

[R.P.McMurphy]

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]

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:BJ4)-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]

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]

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]

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

 

[r.p.mcmurphy]

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]

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

 

[Bob Phillips]

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 :-(