calculate average daily balance from monthly bank statement

[vishwesh barve]

sir,

I need to calculate average daily balance from monthly bank statement with single excel formula. I am facing difficulty to design formula due to below issues
1. multiple transaction for single date
2. closing balance of each date is different.
3. some dates are not having transactions example on sunday there is no bank transactions so closing balance of Saturday to be consider as balance for sunday.
please help mi to design formula for this as I need to calculate daily average balance for every moth as a part of reporting to my seniors.

 

[etaf]

assuming the dates are in column A

then you need to find the earliest date on the sheet and the latest date on the sheet and calculate the number of days
and then divide that into the total spend

=MAX(A1:A4)-MIN(A1:A4)+1
will give you the number of days

sum the total
sum of total spend / (MAX(A1:A4)-MIN(A1:A4)+1)

Average daily spend