calculating earned leave

Our employees earn leave at a percentage of their hours worked. This is
simple enough to set up. If they work 80 hours in a pay period and earn
at the rate of 5%then the formula is simple. However, the spreadsheet
tracks only leave (leave clerks are the only users). Currently, the
leave is calculated assuming that the employee was in a "payed" status
for their entire 80 hours. I'm trying to add the calculation for the
accrual when an employee does not have 80 paid hours in the period. I
can pick up the unpaid leave code with the sumif function but I need to
subtract it from the 80 hours and multiply the total hours paid by the
5%. I know this seems nuts but the leave clerk does not always have the
payroll to know when the employee does not have the full pay period.
She must decipher all this from a leave request. Sounds goofy but it's
how we do it.
So far this has not worked
=sum(80-(sumif(c8:ad9,"b",C8:ad9)),*.05)

=(80-COUNTIF(C8:AD9,"b"))*5%

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"John Smith" <[email protected]> wrote in message
news:[email protected]...
> Our employees earn leave at a percentage of their hours worked. This is
> simple enough to set up. If they work 80 hours in a pay period and earn
> at the rate of 5%then the formula is simple. However, the spreadsheet
> tracks only leave (leave clerks are the only users). Currently, the
> leave is calculated assuming that the employee was in a "payed" status
> for their entire 80 hours. I'm trying to add the calculation for the
> accrual when an employee does not have 80 paid hours in the period. I
> can pick up the unpaid leave code with the sumif function but I need to
> subtract it from the 80 hours and multiply the total hours paid by the
> 5%. I know this seems nuts but the leave clerk does not always have the
> payroll to know when the employee does not have the full pay period.
> She must decipher all this from a leave request. Sounds goofy but it's
> how we do it.
> So far this has not worked
> =sum(80-(sumif(c8:ad9,"b",C8:ad9)),*.05)

It appears that you have both the range to be tested against the
criteria and the sum range grouped together in the C8:AD9 expression.
If I assume the hours are in row 8 and any "b" criteria in row 9, then
=SUMIF(C8:AD8,"b",C9:AD9) will give you the sum of the hours with
criteria "b". Expanding this to give the net result you'd have
=SUM(80-SUMIF(C8:AD8,"b",C9:AD9))*.05

aresen wrote:
> It appears that you have both the range to be tested against the
> criteria and the sum range grouped together in the C8:AD9 expression.
> If I assume the hours are in row 8 and any "b" criteria in row 9, then
> =SUMIF(C8:AD8,"b",C9:AD9) will give you the sum of the hours with
> criteria "b". Expanding this to give the net result you'd have
> =SUM(80-SUMIF(C8:AD8,"b",C9:AD9))*.05
>
Actually the range is all inclusive and scans horizontally. The scope
calculates the sum if the preceding cell has the criteria trigger. This
has worked well for calculating leave usage using the leave codes.
However, using the sumif for calculating accrual has been a challenge.
I had tried the formula like you suggested above but the calculation was
always 80.0. Any other ideas?

Bob Phillips wrote:
> =(80-COUNTIF(C8:AD9,"b"))*5%
>
Thanks, I'll give this one a try.

I'm not envisioning your data. In looking at your original formula, you
are looking at a range consisting of 56 cells (2 rows of 28) and then
summing the same range when cells match the criteria, "b". This is
trying to add "b's" together so you would get zero. I had assumed the
range consisted of 28 values with 28 corresponding keys, "b" being one
of them. Now, I'm even more confused. Could you show me what the data
looks like? Your reference to "preceding cell has the criteria trigger"
suggests another formula is being used within the range.

aresen wrote:
> I'm not envisioning your data. In looking at your original formula, you
> are looking at a range consisting of 56 cells (2 rows of 28) and then
> summing the same range when cells match the criteria, "b". This is
> trying to add "b's" together so you would get zero. I had assumed the
> range consisted of 28 values with 28 corresponding keys, "b" being one
> of them. Now, I'm even more confused. Could you show me what the data
> looks like? Your reference to "preceding cell has the criteria trigger"
> suggests another formula is being used within the range.
>
The preceding cell is the trigger (with "B") and the next horizontal
cell has a numeric value (ie. 8.0). The numeric values following any
cell with "B" is then calculated. This works wonderfully and our leave
clerks only have to enter the appropriate code ("B", "A", etc.) and the
accumulated usage for the pay period is summed, the accrual is added to
the previous balance and the usage is subtracted showing the current
accumulative balance. The accrual chances if an employee is off without
pay and therein lies the challenge. The accrual is a simple percentage
formula based on the assumption that a full pay period is 80 hours.
However, when leave without pay occurs, the accrual formula must reflect
this. That's why I would like the program to assess the pay period for
any unpaid hours and adjust the formula's calculation. It has been a
real stumper. I know what I need it to do but can't seem to make it work.

First of all, I don't understand the use of two rows. Is the second a
continuation of the first?
Anyway, addressing one row, =SUMIF(C8:AC8,"b",D8:AD8) should get you
the sum of all the cells following a "b". Note the two ranges in the
equation are offset by one column which is crucial. The same would be
applied to the second row (if it's a continuation) and you'd have
=SUMIF(C8:AC8,"b",D8:AD8)+SUMIF(C9:AC9,"b",D9:AD9). Now complete the
calculation:
=(80-SUMIF(C8:AC8,"b",D8:AD8)+SUMIF(C9:AC9,"b",D9:AD9))*.05.
Additional consideration would have to be made if it's possible to have
"b" in the last cell in the row (AD8) with the following hours in cell
C9. Rather than going into it here, I'll wait to see if my assumption
holds water.

aresen wrote:
> First of all, I don't understand the use of two rows. Is the second a
> continuation of the first?
> Anyway, addressing one row, =SUMIF(C8:AC8,"b",D8:AD8) should get you
> the sum of all the cells following a "b". Note the two ranges in the
> equation are offset by one column which is crucial. The same would be
> applied to the second row (if it's a continuation) and you'd have
> =SUMIF(C8:AC8,"b",D8:AD8)+SUMIF(C9:AC9,"b",D9:AD9). Now complete the
> calculation:
> =(80-SUMIF(C8:AC8,"b",D8:AD8)+SUMIF(C9:AC9,"b",D9:AD9))*.05.
> Additional consideration would have to be made if it's possible to have
> "b" in the last cell in the row (AD8) with the following hours in cell
> C9. Rather than going into it here, I'll wait to see if my assumption
> holds water.
>
The use of multiple rows in the range is to allow for an employee to use
more than one type of leave on a given day. The "target" range could
easily be noted as (C8:ac9,"b",d8:ad9). It was just easier for me to
remember the same sequence. The way the form is constructed, the clerk
will know not be make a text entry in the AD 8 or 9 cell.
Thanks for your follow up. I am going to try this tomorrow at work
(took a day off today, my boss doesn't like it when I take 5 weeks at a
time so he asked me to stagger the days)(that's the nice part of having
36 years seniority). I will post back if it works. I like the +sumif
feature. Thanks again.
John