# Formula help at work

• Jul 10th 2012, 01:16 AM
Awesomesid
Formula help at work
Hi,

I'm coding a software that prints out the average month to date balance of a customer maintained for any given day.

I do this by calculating daily average balances and storing it in a database

But now I need to give it for a single day without generating it on a daily basis.

My available information on a daily basis is:

1. Opening balance
2. Closing balance
3. Month start balance
4. Day of the month

So if the if today's 10th of July I may have this data for a customer

1st July balance = 10,000
10th July Closing balance = 7000
10th July Opening Balance (9th July's closing balance) = 8000

And its the 10th day, so my average would be calculated from the 1st July to the 10th of July and of course of 1st July avg month to date balance = 10,000

Is there a formula?
• Jul 10th 2012, 12:00 PM
mfb
Re: Formula help at work
You have just these 3 values? In this case, using the average of 1st July (opening?) balance and 10th July closing balance should be reasonable.
You could take the 9th July closing balance into account, but it would not improve the average much. Maybe assume a linear dependence between your values. Here, it would be 9/10*(10000+8000)/2+1/10*(7000+8000) where 9/10 represent the 9 days (out of 10) between the two values and 1/10 is the single day at the end. Note that you can simplify the equation a bit, but it is easier to understand in this shape.
• Jul 10th 2012, 10:25 PM
Awesomesid
Re: Formula help at work
Let me add and correct my previous example

I got 10th July avg balance too...

My real objective is to give a formula to my end user to confirm if the avg balance I'm giving him is correct since at that stage he only has...

My user needs to confirm if the avg balance I'm giving him is correct or not.

Now the trick is that obviously the balance could have changed multiple times, high or low from the 1st of the month to the 10th of the month

To give you guys a better idea how my program calculates daily avg balances here is a snapshot from my database:

Code:

```DATE              DAY        OPBAL        CLSBAL        AVGBAL                Comments 1-Jul-12        1        10000        10000        10000.00        Same as close bal on 1st of month 2-Jul-12        2        10000        10000        10000.00        ((Prv day avg balxPrv day no.) + Close balance) / Day no. 3-Jul-12        3        10000        7000        9000.00                ((Prv day avg balxPrv day no.) + Close balance) / Day no. 4-Jul-12        4        7000        7000        8500.00                ((Prv day avg balxPrv day no.) + Close balance) / Day no. 5-Jul-12        5        7000        7000        8200.00                ((Prv day avg balxPrv day no.) + Close balance) / Day no. 6-Jul-12        6        7000        7000        8000.00                ((Prv day avg balxPrv day no.) + Close balance) / Day no. 7-Jul-12        7        7000        7000        7857.14                ((Prv day avg balxPrv day no.) + Close balance) / Day no. 8-Jul-12        8        7000        7000        7750.00                ((Prv day avg balxPrv day no.) + Close balance) / Day no. 9-Jul-12        9        7000        7000        7666.67                ((Prv day avg balxPrv day no.) + Close balance) / Day no. 10-Jul-12        10        7000        7000        7600.00                ((Prv day avg balxPrv day no.) + Close balance) / Day no.```
But on 10th of July my report is designed in such a away that the user gets all the rows of each customer's position on 10th of July only.

So he has this data on 10th of July.

Date: 10-Jul-12 Day of month: 10 Opening Balance: 7000 Closing Balance: 7000 Avg Balance: 7600.00 Month start balance: 10000

Now how does he verify 7600 is the correct avg balance?
• Jul 12th 2012, 08:38 AM
mfb
Re: Formula help at work
There is no way to check this.
Simple example: Assume that the balance did not change at day 3 (as in your example), but at day 9. The average would be different, but all other entries of day 10 would be the same.