Weighted Average by Month (or Date Range)

**barelyliterate** · 07-16-2018, 02:00 PM

I found a similar thread with a weighted average with a date range, but when I plugged in my own data I got a value error. I will need weighted averages for a specific month in a given year (in this instance, June 2018 but I'll need to recreate for past and future months). The summary page the weighted averages would show up on will have the start and end date of the month so I could reference those cells as a date range as needed.

My spreadsheet will have dates that span over multiple months and years. I can't seem to attach a sample so here's a few rows from this year (but I'll have hundreds of rows spanning several years):

LOAN NUMBER BALANCE RATE DATE
2Z49Q2 568760.86 0.03875 6/12/2018
2Z494Z 840000 0.0375 6/12/2018
2Z49Y5 473687.33 0.0375 6/12/2018
2Z4981 805008.64 0.03625 6/12/2018
2Z5ZZ4 611078.23 0.0375 6/26/2018
2Z5Z2Z 932992.65 0.0375 6/12/2018
2Z758Q 753088.45 0.03125 1/11/2018
2Z7YZ9 484137.16 0.035 1/25/2018
2Z7Y25 534503.54 0.03375 2/22/2018
2Z7YQQ 318020.28 0.03625 3/6/2018

The weight is the balance and I'm averaging the rate. I manually calculate for June 0.03743. Fair to assume columns are ABCD, but really they're different tabs that I can swap out.

Any help would be GREATLY appreciated. I've been staring at this for hours and I'm going to implode.

**Kaper** · 07-17-2018, 05:00 AM

writing it all in one formula we could use for instance:

Formula:

Please Login or Register  to view this content.

But note that we often refer to DATE(2018,6,1)) and to EOMONTH(DATE(2018,6,1),0)) so having this dates written into some cells we couild end up with shorter formula :-)

PS. to attach a sample workbook :
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.