Formula for average daily debit from account data

Copper Contributor

Formula for average daily debit from account data

After exporting account data for a certain date range from a bank account, what would be a good formula for calculating average daily debit? Most dates will have several debit entries, and some dates will not have any entries. Thank you!

3 Replies

Re: Formula for average daily debit from account data

=IFERROR(AVERAGEIF(\$C\$2:\$C\$23,F2,\$D\$2:\$D\$23),"")

You can try this formula. IFERROR returns a blank cell for days without a debit entry.

Re: Formula for average daily debit from account data

Thank you. I see this formula would calculate the average debit amount among several debits posted for each date. But what I meant to ask is how to add up the total of debits for each day in the range, and then calculate the average of total debits per day. I suppose I could Sum the total of debits in the entire column of transactions, then divide by the total number of days in the range to get the daily "spend rate." I should probably include in the average those days where there was no debit. What formula would you use for this inquiry?

Here's an excerpt of dates with one or more debits each day....
6/14/2023 (10.00)
6/14/2023 (50.00)
6/14/2023 (60.00)
6/15/2023 (20.00)
6/15/2023 (25.00)
6/16/2023 (75.00)
6/16/2023 (75.00)
6/20/2023 (300.00)
6/20/2023 (150.00)
6/20/2023 (35.00)
6/20/2023 (35.00)
6/20/2023 (95.00)
6/20/2023 (40.00)
6/20/2023 (30.00)
6/20/2023 (35.00)
6/21/2023 (20.00)
6/21/2023 (45.00)
6/22/2023 (30.00)
6/22/2023 (50.00)
6/22/2023 (30.00)
6/23/2023 (10.00)
6/23/2023 (20.00)
6/23/2023 (45.00)

Re: Formula for average daily debit from account data

``=SUM(B1:B23)/SUMPRODUCT(1/COUNTIF(A1:A23,A1:A23))``

Does this return the intended result? Otherwise can you share the intended result for your excerpt of dates?