Hello,
I have some data where I am trying to create a template. The payment amount is fixed, and deductions are reduced on a future basis. If a date is cancelled, the amount is credited. Would like an output balance and # of transactions, of an inputted date.
Any assistance is appreciated.
Thank you.
I am guessing that H3:I4 are the cells you would like populated.
If those are the cells with which you would like help, then it may help if you could tell us the process you went through manually to come up with the values in those cells.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
No problem let's see. The bill is $15 every time the service is used or two weeks out on a subscription basis.
In this case, I was trying to demonstrate if i had a statement for when I deposited the $200, it would read "Balance as of 7/28/23 $105.00 - " because the future dates would be pre-deducted. Now, if I canceled the service only on July 31st, and I wanted a current balance/statement say on August 4th the $15 from 7/31 would already be included back in plus the next pre-dedicated amounts. Column I was to be a total by month for that balance/statement.
So say a statement from 7/20 would read "Balance as of 7/20/23 -$110.00 13 - May 1 - August" then the "Balance as of 8/04/23 $15.00 12 - May 6 - August".
Does this help?
I feel that I may understand how you derived the values in cells H3:H4 but still not sure why I3 displays 4 and I4 displays 3.
For E2 and down: =IF(C2="Deposit",SUM(D2,E1),E1-H$1)
For H3: =INDEX(E2:E16,AGGREGATE(14,6,(ROW(E2:E16)-ROW(E1)),1))
For H4: =INDEX(E2:E16,AGGREGATE(14,6,(ROW(E2:E16)-ROW(E1))/(C2:C16="Canceled"),1))
Actually it seems to me that if the service was cancelled on 7/31 that the balance should be $135 since the last $15 payment would have been on 7/30.
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks