Running Totals with an Attitude 1

[Vamphyri]

Good day all!

I have no idea where to even start. I would think a few nested Excel Functions would do it, but my brain has thrown in the towel.

I am trying to come up with a quick spreadsheet showing an employee's commission (varies), their expenses (static $1800 per pay period), and the amount we should have paid the employee as a running total.

Here's the kicker. It may be MONTHS before the employee has enough commission to offset his expenses.

I have tried several nested IF statements to no avail.

We have 2 pay period per month (5th & 20th). We only pay our employee if their commissions minus the accumulated expenses equal a positive number.

I'm looking for a way to have Excel (2000) check to see if the commissions are enough to cover the expenses and, if so, subtract the expenses from the commissions. If the commissions do not total enough to pay the expenses Excel needs to enter $0.00. So far so good.

The mind teaser comes in when the employee does NOT have enough commission to cover the expenses for several pay periods. I would like Excel to find the last positive paycheck and start the tally for the remaining months until it comes up with another positive total.

I don't think this is very clear, so please post any questions.

Basic layout of spread is as follows:

Pay Date Employee Commission Employee Running Total Employee Expenses Expenses Running Total Employee Net Commission Paycheck
1/5/2010 $0.00 $0.00 $1,800.00 $1,800.00 ($1,800.00) $0.00
1/20/2010 $4,780.16 $4,780.16 $1,800.00 $3,600.00 $2,980.16 $1,180.16
2/5/2010 $510.10 $5,290.26 $1,800.00 $5,400.00 ($1,289.90) $0.00
2/20/2010 $3,506.38 $8,796.64 $1,800.00 $7,200.00 $1,706.38 $0.00
3/5/2010 $385.20 $9,181.84 $1,800.00 $9,000.00 ($1,414.80) $0.00
3/20/2010 $2,979.46 $12,161.30 $1,800.00 $10,800.00 $1,179.46 $0.00
4/5/2010 $0.00 $12,161.30 $1,800.00 $12,600.00 ($1,800.00)

As you can see the "Paycheck" column has been "hand-written" (no formula).

Any help would be MOST appreciated.

Sincerely

Chris

In the immortal words of Socrates, who said:
"I drank what?

 

[SkipVought]

hi,

when the employee does NOT have enough commission to cover the expenses for several pay periods...

Would you care to quantify that?

ALSO, please post EXACTLY what you want the results to be on each row, for the given example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

OK, I may have found a solution.

In the Paycheck column, your formula, assuming that your table upper-left cell ia A1...
[tt]
H2: =IF(SUM($B$2:B2)-SUM($D$22)-SUM($H$1:H1)>0,SUM($B$2:B2)-SUM($D$22)-SUM($H$1:H1),0)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

sorry, Paycheck is in column G...
[tt]
G2: =IF(SUM($B$2:B2)-SUM($D$22)-SUM($G$1:G1)>0,SUM($B$2:B2)-SUM($D$22)-SUM($G$1:G1),0)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Vamphyri]

Skip,

Thank you so much. Worked like a charm.

Sincerely

Chris

In the immortal words of Socrates, who said:
"I drank what?

 

[SkipVought]

BTW, if you define your table as a LIST, via Data > List > Create List you get several features that you might like...
[tt]
1. You can use the TAB Key rather than the ENTER key to get from one cell to the next. Watch what happens when you TAB at the end of the row of data.

2. Your FORMULAS will automatically appear on a new row, as soon as you enter the data in that row.
[/tt]
Nifty!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}