MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Eric Yokomori on November 26, 2001 6:09 PM

We need a formula that will split a check amount that is equal to or more than $1000. This formula would take into consideration the daily rate and days paid of an individual and divide up the entire pay period into smaller periods so that the entire amount of the check if it is equal to or greater than $1000 is paid out in smaller increments adding up to the total amount and showing the breakdown by pay periods. For example: If the amount of the check is $1205.72 and the daily rate is $76.80, and the total pay period covers 11/2/01 - 11/24/01 the formula could break this amount down into equal portions such as... 11/2/01 - 11/21/01 (the$); 11/20/01 - 11/24/01 (the$). Please help.

Posted by here are the formulas i emailed you... on December 07, 2001 1:06 PM

Check Amount 1800
Daily Rate 76.8
Pay Period Start Date 37197
Pay Period End Date 37219

=IF(F8>=1800,((F11-F10)/3+F10),IF(F8>=1000,((F10+F11)/2),$F$11)) =IF($F$8>=1800,(($E$16-$F$10)*$F$9),IF($F$8>=1000,(($E$16-$F$10)*$F$9),$F$8))
=IF(F8>=1800,((F11-F10)/3+E16),IF(F8>=1000,((F11-F10)/2)+E16,"")) =IF($F$8>=1800,(($E$17-$E$16)*$F$9),IF($F$8>=1000,($F$8-$F$16),""))
=IF(F8>=1800,((F11-F10)/3+E17),"") =IF($F$8>=1800,($F$8-SUM($F$16:$F$17)),"")

Total =SUM(F16:F19)