Forum Discussion
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...
=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.
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)
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)
=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?