Hello! I need an Excel Formula that can compute the following...
I have an excel workbook that contains several sheets specific to a
different account. The format on each sheet is identical: Column A
(Deposits), C (Interest accrued for the month) & E (disbursements) contain
dates while columns B, D, & F contain the amounts for each transaction.
Column G contains the running total for each month B+D-F. I need a formula
that can disperse the amount of interest accrued from the bank for the month
between the different accounts/sheets based on each accounts average daily
balance. For example I accrued $1,146.00 of interest for the month of January
2009. I need a formula to go in column D that divides the $1,146.00 among the
different spreadsheets based on each account's average daily balance on
01/31/2009.

Thanks

Create a summary sheet...
List account nos in Col A
In Col B link each cell to the average balance on each sheet
In Col C have this formula in C1 and copy down
=(B1/SUM(B1:B10))*$D$1 assuming you have 10 accounts...and D1 contains the
figure to be distributed...

Now link the Cells in Col C back to individual sheets...

If you don't know how to then enter
=
in say B1
then click on the sheet you want and then the cell you want on that sheet
and press enter... you will know what formula to enter to link...

You can create formulas which do that without having to have separate
columns but this is easier, can be modified, easily tested and you can have
summary data too


"LisaK112" wrote:

Hello! I need an Excel Formula that can compute the following...
I have an excel workbook that contains several sheets specific to a
different account. The format on each sheet is identical: Column A
(Deposits), C (Interest accrued for the month) & E (disbursements) contain
dates while columns B, D, & F contain the amounts for each transaction.
Column G contains the running total for each month B+D-F. I need a formula
that can disperse the amount of interest accrued from the bank for the month
between the different accounts/sheets based on each accounts average daily
balance. For example I accrued $1,146.00 of interest for the month of January
2009. I need a formula to go in column D that divides the $1,146.00 among the
different spreadsheets based on each account's average daily balance on
01/31/2009.

Thanks

Thank you so much! You are a lifesaver!

"Sheeloo" wrote:


Create a summary sheet...
List account nos in Col A
In Col B link each cell to the average balance on each sheet
In Col C have this formula in C1 and copy down
=(B1/SUM(B1:B10))*$D$1 assuming you have 10 accounts...and D1 contains the
figure to be distributed...

Now link the Cells in Col C back to individual sheets...

If you don't know how to then enter
=
in say B1
then click on the sheet you want and then the cell you want on that sheet
and press enter... you will know what formula to enter to link...

You can create formulas which do that without having to have separate
columns but this is easier, can be modified, easily tested and you can have
summary data too


"LisaK112" wrote:

Hello! I need an Excel Formula that can compute the following...
I have an excel workbook that contains several sheets specific to a
different account. The format on each sheet is identical: Column A
(Deposits), C (Interest accrued for the month) & E (disbursements) contain
dates while columns B, D, & F contain the amounts for each transaction.
Column G contains the running total for each month B+D-F. I need a formula
that can disperse the amount of interest accrued from the bank for the month
between the different accounts/sheets based on each accounts average daily
balance. For example I accrued $1,146.00 of interest for the month of January
2009. I need a formula to go in column D that divides the $1,146.00 among the
different spreadsheets based on each account's average daily balance on
01/31/2009.

Thanks

You are most welcome.

Thanks for the feedback...

"LisaK112" wrote:

Thank you so much! You are a lifesaver!