Formula For Adb

[FERDIE1]

hI everybody,

I would like to ask if you can help me construct a formula for a
AVERAGE DAILY BALANCE(ADB) for the month in a single cell..

the following are the details available:

a.) Transaction Date
b.) Debit
c.) Credit
d.) Balance
where Balance = Credit - Debit


No of
date debit credit balance days
4/1/04 100,000.00 100,000.00 3
4/4/04 50,000.00 150,000.00 11 4/15/04 10,000.00
140,000.00 15 4/30/04 80,000.00 220,000.00 1
ADB = 142,333.33

I am manually computing ADB by getting the no. of days the balanc
remained in that amount. So I multiplied the balance and the no. o
days then get the sum and divide it by the total number of days.
adb = 4270000/30

Hope I explained it well..

 

[Newbie]

Try: (assuming your balance col is B and Days column is C
=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)
HTH

hI everybody,

I would like to ask if you can help me construct a formula for an
AVERAGE DAILY BALANCE(ADB) for the month in a single cell..

the following are the details available:

a.) Transaction Date
b.) Debit
c.) Credit
d.) Balance
where Balance = Credit - Debit


No of
date debit credit balance days
4/1/04 100,000.00 100,000.00 3
4/4/04 50,000.00 150,000.00 11

4/15/04 10,000.00 140,000.00 15
4/30/04 80,000.00 220,000.00 1

 

[FERDIE1]

the formula works... but what i actually want is a single formula wher
it will compute for the no. of days and balance with the given dat
only transaction date, debit, credit and balance...

I don't know if i can create an array formula for this..

Anyway, thank

 

[Newbie]

I am not sure what you mean? Can you post an example?