Average Daily Balance Formula

Hi,

In the attached file, I've calculated the average daily balance based on a date range. I've done this manually. I'm wondering if a formula can be used to do the same calculation? What would it look like?

Thanks in advance.

-Manny

Hi.

If no fancy solution comes up, here's a possibility, using a helper column with this formula in (say) G10 and copy down:

Formula:

Please Login or Register  to view this content.

Then this lookup formula in E3:
=LOOKUP(2,1/(G10:G100<>""),G10:G100)

Good luck!

Thanks for your time and effort. This works somewhat but for some reason, when I change the ending date (E2) to 9/30/23, the G14 cell stay's populated. In this case with $16,619.86. It should be blank or zero.

I've attached updated file with your formula in place.

Thanks

-Manny

The formula will return blank when date in same row AND next row are either earlier than start date or later than end date, or if date column is blank.
Otherwise, it will calculate totals if dates are within range or the daily average in the last row with values.
Please check attached wb with manual calculation revised for new end date.

Well, darn. Not going to work. If the ending date is 2/28/23, it should simply return $10,000 (5 days * 10,000 = 50,000 / 5 days= 10,000). It's a tough one to solve.

I have changed some IF arguments a little and added some conditions to try and contemplate that particular condition you last mentioned.
Please try in G10 copied down.

Formula:

Please Login or Register  to view this content.