+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    08-11-2014
    Location
    NYC, NY
    MS-Off Ver
    2013
    Posts
    0

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

    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
    Last edited by MajorMattMason; 08-11-2014 at 09:17 PM.

  2. #2
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147
    Hello MajorMattMason,

    Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    08-11-2014
    Location
    NYC, NY
    MS-Off Ver
    2013
    Posts
    0

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculate weighted average for values between two date ranges.
    By chan069 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-25-2014, 01:12 AM
  2. sumifs for weighted average
    By amartino44 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-23-2013, 05:43 PM
  3. SUMIFS or SUMPRODUCT with multiple criteria including date ranges
    By baxcat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-25-2013, 09:40 AM
  4. SUMPRODUCT Issue - Need to calculate weighted average of multiple ranges
    By arcobalt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-01-2012, 04:55 PM
  5. Replies: 6
    Last Post: 10-30-2012, 02:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1