SUMIFS()/12 or AVERAGEIFS() formula by DD from YYYY-MM-DD

Hi guys

I hope you are doing great

I would like to do a revenue projection in a chart 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 12 months (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

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...

I'm sure there is an easy formula to take DD in sheet 1 column A in YYYY-MM-DD from sheet 2

Could you help me, please?

Your day numbers in column A are actually text values, rather than numbers. Just fill the column with the numbers from 1 to 31 then apply a custom format to the cells of:

00

to display them in the same way with leading zeros.

Hope this helps.

Pete

It is misleading if you just divide by 12, as your dates in Sheet2 go from June to July of different years. Hence you have some days which occur 13 times in your data and some which occur 14 times. Also, as some months only have 30 days, there are only 8 entries which have 31 as the day number. You can see this if you put this formula in D2 of Sheet1:

=SUMPRODUCT(--(DAY(Sheet2!$A$2:$A$401)=A2))

For a more exact average of your data, you should use this formula in cell B2:

=SUMPRODUCT(--(DAY(Sheet2!$A$2:$A$401)=A2),Sheet2!$B$2:$B$401)/SUMPRODUCT(--(DAY(Sheet2!$A$2:$A$401)=A2))

Hope this helps.

Pete

Thank you so very much, Pete, it does work!! You are my hero :D
Thank you a lot Paul as well