dividing money

G

Glenn

Here's my situation:

I have to deduct a sum of money from my employees over a given number of
paychecks. For instance, I need to deduct a total of $646.00 from Employee
A over 16 paychecks.

If you divide $646.00 by 16, you get $40.375 obviously you can't deduct
that amount.

I need a formula that will show me how much to deduct from check #1 so that
an equal amount is deducted from check #s 2 - 16.


Could someone please tell me how to do this?

Thanks!
 
D

damorrison

Ok I may have something here I am not going to nest the formulas,
A7=646, C7=16 , E7==A7/C7, G7==ROUNDDOWN(E7,0) ,H7==A7-G7,I7==H7/15
 
C

Cutter

The formula for the first check would be:

=646-15*TRUNC(646/16,2) which gives 40.45

The formula for the other 15 cheques would be:

=TRUNC(646/16,2) which gives 40.37
 
P

Peo Sjoblom

Deduct 40.38 from 15 paychecks and 40.30 from the 16th paycheck?

--
Regards,

Peo Sjoblom

(No private emails please)
 
D

damorrison

What ever it's not reocket science!
45.45 once
40.37 times 15
40.37*17=605.55
add 45.45 = 646
 
G

Glenn

What formula did you use to come up with this particular amount?

When I did it out by hand, $40.30 is the amount I came up with for the first
pay check and $40.38 for the 15 remaining paychecks.

I need a formula since the total amount is different for all my employees.
 
R

Ron Rosenfeld

Here's my situation:

I have to deduct a sum of money from my employees over a given number of
paychecks. For instance, I need to deduct a total of $646.00 from Employee
A over 16 paychecks.

If you divide $646.00 by 16, you get $40.375 obviously you can't deduct
that amount.

I need a formula that will show me how much to deduct from check #1 so that
an equal amount is deducted from check #s 2 - 16.


Could someone please tell me how to do this?

Thanks!
Click to expand...

Where Ded equals the total deduction; and n equals the given number of
paychecks:

The amount to deduct from paychecks 2-n is:

=ROUND(Ded/n,2)

The amount to deduct from paycheck 1 is:

=Ded-ROUND(Ded/n,2)*(n-1)
--ron
 
G

Gary''s Student

Here is a general solution:
Let's say you put the total amount in B1 ($640) and the number of pays in B2
(16)
In A1 enter:

=B1-(B2-1)*INT(B1/B2)
this is the amount in week 1

In A2 through A16 enter:

=INT($B$1/$B$2)
these are the amounts for weeks 2 through 16. This approach should work for
any dollar amount and any number of pays greater than 2.
 
G

Gary''s Student

Sorry:



Put one less than the number of pays in B2 and
=B1-(B2)*INT(B1/B2) in A1
 
Top