How to find average with my condition in Excel?

Question

Let's say that this is my bank statement for July 2020:

Sl. No.	Tran Date	Withdrawal	Deposit	Balance Amount
1	01-07-2020		100	100
2	02-07-2020	5		95
3	03-07-2020		500	595
4	06-07-2020	50		545
5	06-07-2020		8000	8545
6	06-07-2020	3000		5545
7	31-07-2020	5		5540
8	31-07-2020		10000	15540

Now I want to find out my monthly average balance.

To find it, I must add all the end of day balances and then divide by the number of days. But, the bank statement does not have end of day balances and just lists the transactions and their respective dates. Days where no transaction took place is not mentioned in the statement.

Now, let's say that I have an Excel sheet of my bank statement. How can I find the monthly average balance? I read something about AVERAGEIF but I am not sure if it will be helpful in my case.

---

This is what I do to find the average (but it's a lengthy procedure):

1. I look into the month's bank statement and then find out the final transaction of each day which will help me find the end of day balance.

2. Now I will open a new Excel sheet and mention the end of day balances like this:

   Date	End of Day Balance
   01-07-2020	100
   02-07-2020	95
   03-07-2020	595
   04-07-2020	595
   05-07-2020	595
   06-07-2020	5545
   07-07-2020	5545
   08-07-2020	5545
   09-07-2020	5545
   10-07-2020	5545
   11-07-2020	5545
   12-07-2020	5545
   13-07-2020	5545
   14-07-2020	5545
   15-07-2020	5545
   16-07-2020	5545
   17-07-2020	5545
   18-07-2020	5545
   19-07-2020	5545
   20-07-2020	5545
   21-07-2020	5545
   22-07-2020	5545
   23-07-2020	5545
   24-07-2020	5545
   25-07-2020	5545
   26-07-2020	5545
   27-07-2020	5545
   28-07-2020	5545
   29-07-2020	5545
   30-07-2020	5545
   31-07-2020	15540

3. Finally, I will use the AVERAGE function. So, the July month's average is 5036.935484

This is quite a tedious process. Is there any way to make this easier?

Answer 1

Assuming your table starts in A1 and ends in E9, but can be much, much longer:

• make sure the cells in date column B are formatted correctly as date.
• In cells H2:H13 write the first date in each month (1/1/2020, 1/2/2020... 1/12/2020)
• Format cells H2:H13 to display the month instead (custom -> mmmm). Now the H2:H13 cells should show the month name.
• in cell I3, write this formula: =IFERROR(AVERAGEIFS($E$2:$E$3000,$B$2:$B$3000,">="&$H2,$B$2:$B$3000,"<="&EOMONTH($H2,0)),"-")
• copy formula in the cells below on I4:I13

Now, I think i added enough rows in the formula added from E2 to E3000 and from B2 to B3000 but you can change those "3000" to more if required, if you have more rows...

Also depending on your Excel version and regional settings you might need to replace every comma from the formula (,) with a semicolon (;). I've seen some versions of excel require a semicolon instead of a comma.

I tested the formula before putting it here and I confirm it works. For the missing months or without any data the formula will show a "-".

NOTE: if you have more years extracted, you need to modify the range to have only 1 year otherwise it will calculate the average for september for example from multiple years. For every year, add 12 more cells in column H with the same formula but with the range for the respective year.

Answer 2

Your method is what needs to be done.

One way to make it easier would be with a VBA macro or Power Query to do the same thing.

Here is a Power Query solution:

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm

M Code

Edit1: Better running total algorithm
Edit2: Differentiate by year
All we do is add the year to the month name in the step that generates that

let

//Replace Table Name in the next line with actual table name in your workbook
    Source = Excel.CurrentWorkbook(){[Name="Table30"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Sl. No.", Int64.Type}, 
        {"Tran Date", type date}, 
        {"Withdrawal", Currency.Type}, {"Deposit", Currency.Type}, {"Balance Amount", Currency.Type}
    }),

//Replace nulls with 0 because cannot add nulls
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Withdrawal", "Deposit"}),

//get starting balance. Should be last entry of previous month
//but for here I will set it to 0
    startingBalance = 0,

//Group by dates and get net of Withdrawal/Deposits for each date
    group = Table.Group(#"Replaced Value","Tran Date",{
        {"Net Change", each List.Sum([Deposit]) - List.Sum([Withdrawal]), Currency.Type}
    }),

//create list of all dates in the statement
    monthDates = Table.FromList(
            let 
        mnthStart = Date.StartOfMonth(List.Min(#"Replaced Value"[Tran Date])),
        mnthEnd = Date.EndOfMonth(List.Max(#"Replaced Value"[Tran Date]))
            in List.Dates(mnthStart,Duration.Days(mnthEnd - mnthStart)+1, #duration(1,0,0,0)),
                Splitter.SplitByNothing(),{"Month"}),

//Merge with the dates from the grouped table
// and sort to ensure proper date order
    allDates = Table.Join(group,"Tran Date", monthDates,"Month",JoinKind.RightOuter),
    #"Sorted Rows" = Table.Sort(allDates,{{"Month", Order.Ascending}}),

//Remove partial date column 
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Tran Date"}),

//Add running total column = end of day balance
bl = List.Buffer(List.ReplaceValue(#"Removed Columns"[Net Change],null,0,Replacer.ReplaceValue)),
RT = List.Generate(
        ()=>[rt=bl{0}, idx=0],
        each [idx] < List.Count(bl),
        each [rt = [rt] + bl{[idx]+1}, idx = [idx]+1],
        each [rt]
),
rtTable = Table.FromColumns(
            {#"Removed Columns"[Month],RT},
            {"Date", "EOD Bal"}),

//Convert date to month name
//Add year to differentiate by year
    #"Added Custom1" = Table.AddColumn(rtTable, "MonthName", each Date.MonthName([Date]) & Date.ToText([Date]," yyyy")),

//Group by monthName and aggregate by average
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"MonthName"}, {{"Average Daily", each List.Average([EOD Bal]), type number}}),

//set data types
    typed = Table.TransformColumnTypes(#"Grouped Rows",{{"MonthName", Text.Type},{"Average Daily", Currency.Type}})
in
    typed

Original Data

Results