0

I have daily data entries for 3 indices. They do not feature every day of the month and for some days entries for one of the indices will miss; the quantity of cells per month differ. I want to calculate the average value for these individual indices starting the 16th of the prior month and going to the 15th of the current month (or the first date after the 15th/last day before the 16th if there is no entry). For example, in the screen grab below, the average for MXWO in the month of February would be the average of the array B14:B36 (and for January, B2:B13).

What my data looks like

I want to produce 4 columns: Date (Month-Year), average of MXWO, average of JPMIGHYS, and average of USTWBGD. Each of these averages needs to use the methodology described above. How might I achieve this?

I've tried using the OFFSET function, but since the cell increment changes month to month (there is a different amount of missing cells per month), this didn't work.

eenmooiding
  • 1
  • 1
    Use `AVERAGEIFS` – Ron Rosenfeld Apr 20 '21 at 00:45
  • I have tried, but for some reason it won't return a value to me (and I do not know how to make it go from the 16th to the 15th of each month – eenmooiding Apr 20 '21 at 01:07
  • I don't see in your question any example of what you have tried. – Ron Rosenfeld Apr 20 '21 at 01:15
  • I am sorry for not including that, I tried to use the method described here: https://stackoverflow.com/questions/33769790/calculate-monthly-average-from-daily-data-without-pivottable. I also tried using the OFFSET function but as I mentioned in the question that did not work for me. – eenmooiding Apr 20 '21 at 01:28
  • 1
    Without seeing how you tried to apply it, I have no idea where you went wrong. You also don't show how you would structure your results. I suggest you read read the HELP pages for information as to [How to Ask a Good Question](http://stackoverflow.com/help/how-to-ask), and [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve); then edit your question so we can help you. – Ron Rosenfeld Apr 20 '21 at 02:09
  • what version of Excel are you using? – Terry W Apr 20 '21 at 02:51
  • I am using Excel version 2103, I will include some of my attempts in a future edit – eenmooiding Apr 20 '21 at 03:28
  • @RonRosenfeld thank you for pointing me in the right direction. I ended up adding in 2 columns for each month (start date and end date) and used =AVERAGEIFS('Raw Data'!$B$2:'Raw Data'!$B$3776;'Raw Data'!$A$2:$A$3776; ">="&'Raw Data'!J2; 'Raw Data'!$A$2:$A$3776; "<="&'Raw Data'!K2), where my Raw Data tab includes the data for the indices, and I make sure the daily entries are matching the days I want in the AVERAGEIFS command. – eenmooiding Apr 20 '21 at 20:00

0 Answers0