Google spreadsheet formula projection

[Perlapimpim]

Hi guys

I hope you all are having a nice summer

I would like to do a chart projection based on date (day of the month) and revenue:

I need a formula to calculate the average revenue of each day of the month for the past year (sheet 2) and then project on a chart what I should achieve. For exemple the first day of this month should be above $1,637.81 https://docs.google.com/spreadsheets...it?usp=sharing

Could you help me please?

[MrShorty]

My first thought is that this is an easy SUMIFS()/12 or AVERAGEIFS(), if there was a column with "day of the month" in sheet 2. So, I go into sheet2 and add a day of the month column using the DAY() function ( https://support.google.com/docs/answer/3093040 ). =day(A2) in C2.

Then the sum becomes a relatively simple SUMIFS() function =sumifs(Sheet2!$B$2:$B$401,Sheet2!$C$2:$C$401,A2,Sheet2!$A$2:$A$401,">="&edate(today(),-13))/12 or averageifs() with the same criteria.

I wasn't exactly sure how you wanted to formulate the "for the past year" part of the criteria. I went with 13 months before today, but you may want the eomonth() function (https://support.google.com/docs/answer/3093044 ) to get the last day of the month 13 months before today or some other logic. You may need to spend a few minutes thinking more carefully about what exactly you mean by "for the past year" as you formulate that criteria in the final function.

[Perlapimpim]

Hi,

Thank you so much for your help. I forgot to mention that I can't change Sheet 2 as it's automatically generated by a script every day. So even if I change it today it will get overwritten the day after...

[MrShorty]

I can't change Sheet 2 as it's automatically generated by a script every day.

And this script cannot be modified (or you cannot request a modification of the script) so that it adds the extra column? or does not overwrite the helper column? or to add the extra column after writing the data to the sheet? Could you add the helper column after the data is written to the sheet? Could the helper column be kept in sheet1? That helper column plus a sumifs() or averageifs() still seems like the easiest way to me.

If the "day of month" is really not possible, I could see a sequence of 12 VLOOKUPS() summed up, using a combination of today(), edate(), and/or eomonth() that would do this.
=vlookup(eomonth(today(),-1)+1,sheet2!$A$2:$B$2000,2,TRUE)+vlookup(eomonth(today(),-2)+1,sheet2!$A$2:$B$2000,2,TRUE)+more vlookups

[Perlapimpim]

Unfortunately, I can't change sheet 2 at all.
I'm sure there is an easy formula to take DD in sheet 1 column A in YYYY-MM-DD from sheet 2