Weighted average based on various date ranges (SUMIFS or SUMPRODUCT)?

**MajorMattMason** · 08-11-2014, 09:15 PM

I need to calculate a fiscal year rate based off of multiple contract year rates. Contract years will not overlap, but they can start and end on any date.

My problem is too complicated to explain entirely in writing. I hope the attached table is able to demonstrate my issue.
In the attached sheet, Contract A is what is primarily giving me trouble. In this example, I need a single formula or series of formulas that can calculate the rate for fiscal year 2014 by taking the weighted average of the rates from the 3 contract years that include parts of 2014. I need the formula to work for multiple scenarios, some of which are provided in the attached spreadsheet. The spreadsheet gives a more thorough description as well as some additional restrictions/requirements, which are also stated below. A long hand version of the desired solution is included towards the bottom of the spreadsheet.

Additional Catches:
A single formula must work for all contracts.
No VBA. Unless it is insanely easy, fast, and does not need changing should the table change (e.g. the column moves)
No array formulas. If someone else is using this table and they click into the cell I do not trust them to use Ctrl+Shift+Enter.
You cannot assume that these will always be in order, which means use of MATCHing is out. I usually use various forms of INDEX in conjunction with the other data for a given contract to find what I need.
Many rows are frequently added and removed, so assuming the other data remains the same (and logical), it should be able to handle a row being deleted, added or moved. Again, I use INDEX to handle this.
Clearly the Contract Years and Fiscal Years do not align.
This rate calculation has to be done at least 3 times per fiscal year (3 different annual rates), so I am also looking to find the way to do it with the fewest formulas.
Assume 1,000 contracts averaging 5 years each. And there are many other columns that were excluded because they are not relevant here. So the table can get large.

Thank you anyone and everyone in advance for your help. I have been racking my brain on this one longer than I should have been.

Contract Year Rates to Fiscal Year Rates.xlsx

**arlu1201** · 08-11-2014, 09:30 PM

Hello MajorMattMason,

Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.

**MajorMattMason** · 08-14-2014, 09:34 PM

Never mind. I'm an a bit slow on multiple counts. First, i posted in the wrong section. Second, all i had to do was identify during which contract year the fiscal start date was (easy) and that was my first rate, and do the same with the fiscal end date to find the second rate. I'll do better next post.