Monthly Reporting Balance

[sabino]

Hi, just came across this forum, and hope you can help.

My problem is as follows:
1. Each month I have to send out a Report to a list of partners that states the revenues they generated that month, and their balance.
2. If their balance is equal or above $50, I pay them the full balance.
3. If the balance is below $50, it accumulates until at least $50 is reached.
4. When their balance is paid out, it goes back to "0" and starts building up again until it reaches at least $50 again, and so on.
5. Each month the balance needs to be calculated so that it deducts previous payments if there were any.

Finally, this will be imported to a Google Spreadsheet so best if simple Excel formulas are used and no Macros or other advanced programming (even if the formula in the end is much longer), as Google Spreadsheets is more limited than Excel, and I need to have the file online as it links to other databases and mail merge tools.

I've been doing the calculations by hand, but as things have been growing, I've felt the need to automate the calculations. It's rather easy to get this done with 10/20 partners, but not as much when you are over 200.

It seems as a fairly simple problem, but I'm not being able to set the formula straight.

I've tried to use the AddIn from MrExcel html maker but I have Excel 2013 and could not find it on the Ribbon after loading the Addin. I've made available the example on a Dropbox file - apologies, hope you can still help.
Here is the Dropbox link: https://dl.dropboxusercontent.com/u/77402/example.zip

In the example I'm posting, I've set several blocks of lines to illustrate the behaviour of different partner accounts over the year so that you can see how the issue evolves in different partner accounts.
Again, here is the Dropbox link: https://dl.dropboxusercontent.com/u/77402/example.zip

Hope anyone can help.

 

[AkaTrouble]

this formula copied into the adjoining cell would transfer the balance to the next month if lower than $50 otherwise transfer nothing

=IF(A2<50,A2,"$0.00")

this is first formula based on february line one once entered it could be copied across and down for all the fields

 

[sabino]

Hi,
Thanks for your help!
Your answer is correct, and would work except I need to have this formula on a single column at the end of the 12 months so that I can retrieve the total always from the same cell. The lines on each month are entered values, but the balance at the end (the red column on the example) needs to be calculated so that it always accurately translates the updated balance, whether it is above or under $50, as the year's results get populated, and deducts any payments that were made in previous months.
Thanks!

 

[AkaTrouble]

ok couple of questions

do you clear the previous months figures before calculating

could you have an extra column that has payments made year to date so you could sum all months and subtract year to date paid

or have a current month field so that you could sum previous months and deduct this from sum of total months

or even to have two fields per month total and amount paid as a negative

sort of trying to find are you set on your layout or willing to have a new layout achieving your desired result

 

[sabino]

Hi, I really need to keep the current layout as there are several other links to this spreadsheet, so unless it is completely impossible, I would keep the current layout.
I don't clear any values, the spreadsheet is populated with the monthly data and and the values need to remain for future reference. Thanks!

 

[AkaTrouble]

not sure if there is simpler way and your formula seems to work in column N

writing a formula that excludes all previous values under $50 i came up with this (long winded) formula

=(SUM(A2:L2)-(SUM(IF(A2>50,A2,0),IF(B2>50,B2,0),IF(C2>50,C2,0),IF(D2>50,D2,0),IF(E2>50,E2,0),IF(F2>50,F2,0),IF(G2>50,G2,0),IF(H2>50,H2,0),IF(I2>50,I2,0),IF(J2>50,J2,0),IF(K2>50,K2,0),IF(L2>50,L2,0))))

which adds together all values over $50 and takes them away from total value of months

 

[AkaTrouble]

the issue i am having that the monthly values it transferred to following month would have to be set to zero for previous months otherwise they increase the total

 

[sabino]

Hi Aka, thanks for trying and for the code example. I will test the formula on Sunday morning and post here the results and other thoughts.
Thanks!

 

[sabino]

Hey Aka,
Thanks again for your help.
I couldn't get your formula to work, not entirely.
I eventually had to sacrifice my layout to get it all working. The final solution was found having a column do the balance each months and deducting it if it was equal/above 50. So, ad although simple, the layout was the problem so I had to get the developer to put in some extra time!

Thanks anyway for your help and I will be stopping by to return the help to other users if I can!