Help with Payroll Formulat Needed

[smonczka]

Hello.

I'm trying to create a spreadsheet that tracks vacation pay and sick
pay for the HR department.

The problem I am running into is one of formula syntax. For sick time
we start accruing from the date of hire, but an employee doesn't
become eligible to use it till after they have been here for 90 days.
We accrue sick days at .013699 per day worked and cap the number of
accrued sick days at 10 no matter how many days the employee has
worked. I'm having problems inclding the cap in the syntax of the
formula

So what I have is...

Cell D5 is the Number of Days worked.

In cell D6 I have the formula to calculate the total number of sick
days accrued =IF(D5<90,0,IF(D5>90,D5*0.013699))

So if Number of Days worked is less than 90 then 0 sick days are
accrued. If Number of Days worked is more than or equal to 90 then
multiply the Number of Days worked by .013699 to arrive at the total
sick days accrued.

Problem is I don't know how to include a cap in the equation of 10
sick days accrued if the total sick days accrued is 10 or more.

Any ideas out there?

Thanks all!

Steve

 

[Peo Sjoblom]

One way

=MIN(10,IF(D5<90,0,IF(D5>90,D5*0.013699)))

 

[CarlosAntenna]

Peo is right to wrap a MIN around your existing formula, but I think your
formula could be simplified if you eliminated the second IF like this:

=IF(D5<90,0,D5*0.013699)
=MIN(10,IF(D5<90,0,D5*0.013699))

This also eliminates the "hole" in your formula if D5=90.

-- Carlos

 

[smonczka]

Thanks all, that worked great.