Annual Reset

Question

Hello,

I am trying to create a spreadsheet that will track an annual reset date (or month).  In our office people pay a certain percentage per transaction until they reach a cap, after the cap they don't pay anything until the reset date.  So far I've created the spreadsheet to include several columns, three of which are: Month (E), Amount Paid This Transaction (K), Remaining to Pay After This Transaction (L), with each row being a new transaction.  Column K is dependent on Column L because the sum of Column K cannot be more than the cap amount until they have reset.  Column L is dependent on what they've paid in previously, and also their reset date.  The formula I have for Column K is =IF(L18>0,IF(SUM((I19-J19)*0.3)>L18,L18,SUM((I19-J19)*0.3)),0).  Where I get into trouble is with the formula for Column L.  Currently the formula I have is =IF(SUM(L18-K19)>0,SUM(L18-K19),IF(SUM(L18-K19)<0,0,SUM(L18-K19)));  I'm having trouble associating it with the reset date since it only resets once a year.  All of the solutions I've come up with tend to screw up the previous or subsequent rows.  Any help would be appreciated!

Thanks,

Herb

Answer 1

Thank you!  This was very helpful.  I had to make a couple of tweaks to the formulas in order to get exactly what I needed, but for the most part the concepts you mentioned solved my problem!

Answer 2

This solution is based on the following important assumptions:

• Only the month and day of the reset date are important (not the year), and are the same for every year
• The cap amount is the same every year
• Transactions for each person are in a separate list
• Transactions are in order by date, from oldest to newest

If any of these assumptions is incorrect, we'll have to tweak the formulas.

I decided to add a "helper column" to check if the reset date has passed since the previous transaction.  (the formula is sort of long and the result is used in the formulas for commission amount and remaining commission.)  You could hide the column if you want, or roll the formula into the other two formulas.  Here's what I ended up with:

Here are the formulas:

D2:  =MIN(0.3*C2, IF(F2, $A$4, E1))

E2:  =IF(OR(F2, NOT(ISNUMBER(E1))), $A$4, E1) - D2

F2:  =AND(B1 < DATE(YEAR(B2), MONTH($A$2), DAY($A$2)), B2 >= DATE(YEAR(B2), MONTH($A$2), DAY($A$2)))

These formulas can be copied down for each transaction.

I hope this is what you need.

Answer 3

Sure, thank you!  I'm not sure if I can explain it well enough, but I'll try!

So let's say A2 contains the reset date (2/1/10), A4 is the cap amount (1,000), column B contains the transaction dates of all subsequent transactions, column C contains the transaction price for all transactions, column D contains the commission paid, and column E contains the remaining commission to pay (based off the cap):  

I'm hoping to find formulas for both column D & column E.  The formula for column D needs to calculate the commission paid based off 30% of the transaction price (column C), the remaining commission (column E), & the reset date (A2); the formula for column E needs to calculate the remaining commission to be paid before reaching the cap, and is based on the cap amount (A4), previously paid commission amounts (column D) & the reset date (A2).

In the above picture, the commission paid for the 1st transaction is 300 (D2=.3*C2).  However, in the 2nd transaction, 30% of the transaction price would yield a higher amount than the remaining commission, so the commission paid ends up being 700 (D3=E2).  Finally, in the 3rd transaction, the Transaction Date matches the Reset Date.  The commission paid amount ends up being 450 (D4=.3*C4), since 30% is now less than the remaining commission as a result of the cap reset.

I hope that clarifies my question a little bit better!  I'm hoping to find formulas that I can copy throughout the entire column since the typical amount of transactions ranges in the hundreds.

Thank you for your help!

Answer 4

Use SUMIF, SUMIFS, or an array formula using SUM.  If you provide some example data and output, I (or others) can be more specific.