Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
I am looking for a payroll formula. The formula will combine data from two
separate pay periods. For each day worked, employee receives $65 not to exceed $1,300 in total from two pays. Example: Payroll 1, employee works 12 days and receives $780.00 ($65 x 12) Payroll 2, employee works 12 days, this works out to $780, but exceeds the maximum $1300 payment allowed. ie: The second pay needs a result of either the actual calculation if the total of 2 pays is 1300 or less or a calculation of 1300 less 780, indicating the actual payment allowed, ie: $520 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
arlene,
this should do the trick. do let me know if it works. paycheck for second period = if(sum(P1,P2)<=1300, P2, 1300-P1) P1 - payroll for period 1 P2 - payroll for period 2 regards, "Arlene" wrote: I am looking for a payroll formula. The formula will combine data from two separate pay periods. For each day worked, employee receives $65 not to exceed $1,300 in total from two pays. Example: Payroll 1, employee works 12 days and receives $780.00 ($65 x 12) Payroll 2, employee works 12 days, this works out to $780, but exceeds the maximum $1300 payment allowed. ie: The second pay needs a result of either the actual calculation if the total of 2 pays is 1300 or less or a calculation of 1300 less 780, indicating the actual payment allowed, ie: $520 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
This worked perfectly, thank you very much.
"Kiran" wrote: arlene, this should do the trick. do let me know if it works. paycheck for second period = if(sum(P1,P2)<=1300, P2, 1300-P1) P1 - payroll for period 1 P2 - payroll for period 2 regards, "Arlene" wrote: I am looking for a payroll formula. The formula will combine data from two separate pay periods. For each day worked, employee receives $65 not to exceed $1,300 in total from two pays. Example: Payroll 1, employee works 12 days and receives $780.00 ($65 x 12) Payroll 2, employee works 12 days, this works out to $780, but exceeds the maximum $1300 payment allowed. ie: The second pay needs a result of either the actual calculation if the total of 2 pays is 1300 or less or a calculation of 1300 less 780, indicating the actual payment allowed, ie: $520 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
Try this:
=IF(Pay1+Pay21300,1300-Pay1,Pay2) -- Biff Microsoft Excel MVP "Arlene" wrote in message ... I am looking for a payroll formula. The formula will combine data from two separate pay periods. For each day worked, employee receives $65 not to exceed $1,300 in total from two pays. Example: Payroll 1, employee works 12 days and receives $780.00 ($65 x 12) Payroll 2, employee works 12 days, this works out to $780, but exceeds the maximum $1300 payment allowed. ie: The second pay needs a result of either the actual calculation if the total of 2 pays is 1300 or less or a calculation of 1300 less 780, indicating the actual payment allowed, ie: $520 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
If days worked are in D1 and D2, then:
P1: =max(1300,65*D1) P2: =min(65*D2,1300-P1) ----- original message ----- "Arlene" wrote: I am looking for a payroll formula. The formula will combine data from two separate pay periods. For each day worked, employee receives $65 not to exceed $1,300 in total from two pays. Example: Payroll 1, employee works 12 days and receives $780.00 ($65 x 12) Payroll 2, employee works 12 days, this works out to $780, but exceeds the maximum $1300 payment allowed. ie: The second pay needs a result of either the actual calculation if the total of 2 pays is 1300 or less or a calculation of 1300 less 780, indicating the actual payment allowed, ie: $520 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
Errata....
"JoeU2004" wrote: If days worked are in D1 and D2, then: P1: =max(1300,65*D1) P2: =min(65*D2,1300-P1) Duh, P1 should be =min(1300,65*D1). ----- original message ----- "JoeU2004" wrote in message ... If days worked are in D1 and D2, then: P1: =max(1300,65*D1) P2: =min(65*D2,1300-P1) ----- original message ----- "Arlene" wrote: I am looking for a payroll formula. The formula will combine data from two separate pay periods. For each day worked, employee receives $65 not to exceed $1,300 in total from two pays. Example: Payroll 1, employee works 12 days and receives $780.00 ($65 x 12) Payroll 2, employee works 12 days, this works out to $780, but exceeds the maximum $1300 payment allowed. ie: The second pay needs a result of either the actual calculation if the total of 2 pays is 1300 or less or a calculation of 1300 less 780, indicating the actual payment allowed, ie: $520 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formula to copy/paste formula needed please. | Excel Discussion (Misc queries) | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
How do I view formula results intead of formula in excel? | Excel Worksheet Functions |