• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Variable Sum and Avg based on 2 criteria in table

AZExcel

Member
Hello,

I am working on a project involving determining a factor based on a CPI table.

The solution I am seeking involves 2 pieces of data, one of which I have worked out using match and index and some other formulas.

The 2nd part is a little more challenging. I need to be able to select usually from the same row( yr) a starting month and ending month, for example, Jan 2020 thru May 2020 and determine the avg.

The user will select the Months using data validation which I already have set up. But I cannot conceptualize how to have it give me an avg for a period of time selected. Any help will be appreciated.

Below is a link to the file
 

Attachments

  • Awards-August CPI-Tool 2020.xlsx
    33.8 KB · Views: 12
This will not be of direct value unless you have access to Excel 365. The steps could, however, be reproduced in traditional Excel by using helper ranges or defined names to hold the array formulas.
Code:
= LET(
  startDate,   DATEVALUE(1&"/"&StartMonth&"/"&StartYear),
  currentDate, DATEVALUE(1&"/"&CurrentMonth&"/"&CurrentYear),
  elapsed, DATEDIF(startDate,currentDate,"m"),
  dateSeq, EDATE(startDate, SEQUENCE(1, elapsed+1, 0)),
  monthSeq, TEXT(dateSeq, "mmm"),
  yearSeq,  YEAR(dateSeq),
  rownum, XMATCH(yearSeq,  Year),
  colnum, XMATCH(monthSeq, Month),
  selectedData, INDEX(DataTable,rownum, colnum ),
  AVERAGE(selectedData) )
The sequence function is the hardest to reproduce, but one could use
Code:
=ROW(INDIRECT("1:"&elapsed)
 
Peter,

Thank you for taking the time to respond to my question. But the solution you offer is outside of my skill level. How would I go about applying the above?
 
What the above does is generate a sequence of dates corresponding to the months over which you need to aggregated data. If you don't have access to Excel 365 it is going to be a bit of a challenge to regress the solution for older versions of Excel.

Another approach is to build a helper range on a new sheet that contains the date corresponding to each datum point in the original table. That would allow you to look up the data or aggregate it over any date range you choose, including ranges that span multiple years.
Code:
= SUMIFS( DataTable,
  DateCriterionRange, ">=" & DATE(YEAR(currentDate),1,1),
  DateCriterionRange, "<=" & currentDate )
I have added some calculations to your workbook using a green fill to identify them.
 

Attachments

  • Awards-August CPI-Tool 2020 (SUMIFS).xlsx
    44.7 KB · Views: 8
Peter,

Thank you for helping me with this project. I see that your work accurately sums up the dates that are selected on each side of the sheet. This was exactely what I needed. In addition to adding up all the factors from point A to point B ( so to speak) I will need the Avg. I should be able to work this out along with the additional work I need to do to come up with the factor to complete the Roll Back.

Thank you very Much
 
Back
Top