Running Sums

[gladiator13]

I have a report that adds up Agent's Commissions for all commissions agents earn for our company throughout the year. We allow tax free commissions of up to $1500 dollars and then start taxing them after that point on any commissions earned above the tax free commissions allowed in this case being $1500 times the tax rate. Now the real question, I have a report that gives me the amount of commissions earned per agent in one column and the running sum in the next, so I thought the logical equation to build was:
IF the running sum of the agents commissions earned goes above the allowed tax free commission(1500) then multiply the tax rate times those commissions earned at that point in time. However when the running sum goes above the tax free commissions(1500)for the first time the equation will multiply the tax rate times those commissions earned in that point in time NOT giving the leeway for both the commissions earned and the running sum the amount of tax free commissions. I think my example below will clear what I am saying up


MY automated equation is

IF
YEAR TO DATE COMMISSIONS > TAX FREE ALLOWABLE then
WITHOLDINGS=AGENTS COMMISSIONS EARNED * AGENT TAX RATE
ELSE
WITHOLDINGS=0.00

WHICH PRODUCES THIS

1st RECORD FOR AGENT 1

AGENTS COMMISSIONS EARNED: $297.31
YEAR TO DATE COMMISSIONS: $297.31
COMMISSION WITHHOLDING: 0.00-correct since it is not above the tax free commissions of $1500

2nd Record for Agent 1
AGENTS COMMISSIONS EARNED: $1448.74
YEAR TO DATE COMMISSIONS: $1746.05
COMMISSION WITHHOLDING: 101.41- INCORRECT, the tax free allowable commission is $1500 so since the YEAR TO DATE COMMISSION is above my tax free allowable amount it multiplied the tax rate(7% in this instance)times the commissions earned in that point in time of $1448.74 not allowing the leeway of $1500 allowed!

So for the first record in which the year to date commissions earned goes above the allowable tax free commissions it should just multiply the tax rate times the amount of money above the allowable amount

YTD-$1746.05-$1500.00( TAX FREE COMMISSIONS)so only
$246.05 should be taxed.

PLAIN AND SIMPLE: HOW DO I GET MY EQUATION TO DO THIS STARTING ON THE FIRST RECORD THAT GOES ABOVE THE $1500 TAX FREE AMOUNT!!

I AM DESPERATE!!!

THIS SOUNDS VERY COMPLICATED I KNOW!!! HELPPPPPPPP!!!!!

 

[autocutter]

IF
((YEAR TO DATE COMMISSIONS)-1500) > TAX FREE ALLOWABLE then
WITHOLDINGS=AGENTS COMMISSIONS EARNED * AGENT TAX RATE
ELSE
WITHOLDINGS=0.00

 

[gladiator13]

Thanks, The equation is logical but the tax rate has to be only multiplied by that YTD amount over the tax free commission, so since the YTD in my example is 1746.05, only 246.05 would have to be multiplied times the tax rate. The 1st time the agent earned the commission it was $297.31 and the second time was $1448.74 equaling the YTD amount of $1746.05. Hence since for the second record for this agent the YTD goes over the tax free allowable by over $246.05 only the $246.05 would have to multiplied times the tax rate. How would I do this?
Thanks.

 

[autocutter]

Based on only what your saying:

IF
((YEAR TO DATE COMMISSIONS)-1500) > TAX FREE ALLOWABLE then
WITHOLDINGS=((YEAR TO DATE COMMISSIONS)-1500) * AGENT TAX RATE
ELSE
WITHOLDINGS=0.00

However, if you really mean to calculate WITHHOLDINGS on an individual bases(BY TRANSACTION) and AGENTS COMMISSIONS EARNED is not a running sum yhen you have a different problem then what you are saying.