Formula to calculate averages by date intervals

[sathya]

Hi hans and rudi,

Please look at the attachment and any help is much appreciated.

Thanks a lot
sathya

[HansV]

See the attached version.

auto generator.xlsx

[Rudi]

    
Attached...

auto generator.xlsx

(Oh...Hans pipped me! Take your pick....)

[sathya]

Thanks a lot for your reply. Please look at the attachment. for some reason the formula is not calculation average.

Thanks a lot again hans

[Rudi]

You must just check that the formula references include the extra dates you input...

auto generator.xlsx

[sathya]

Thanks for your reply hans.

How does formula generate automatically when ever i go to new line?

Thanks

[HansV]

This version has formulas up to row 100. If you need more, you can easily modify them.

auto generator.xlsx

[sathya]

Thanks again hans :)

[Rudi]

I'm certainly not trying to out do Hans, but here is just another option using Excel's tables that have a different and more dynamic reference style.
Tables will also update these dynamically if you add new records to the bottom of the table.

auto generator.xlsx

[sathya]

Hi Hans and Rudi,

Please look at the attachment.

Thanks in advance :)

[Rudi]

Attached...

auto generator.xlsx

[sathya]

Hahaha

Hans and Rudi u guys are brilliant. Thanks Rudi :)

[HansV]

All your previous examples used dates within the current year. You now introduce dates in last year too. The formulas don't take that into account. We have to change them to look at the year as well as at the month.

Apart from that, I don't understand why you changed the formulas in F1:Q1 to use text...

See the attached version.

auto generator.xlsx

[sathya]

Hi hans,
The reason why i changed formula in F1:Q1 is i would like to go via month wise. Say example current month is April : i would like my F1 to start with March and so on. Other example, current month Dec, i would like to start my F1 in Nov at this stage hans. Thats the reason why i changed it. Thanks again.

Other issue is i am pulling information from access to excel with the above attached template. But every time one or two cells in excel is automatically changing its format to dd/mm/yy and not staying with number format. Any idea?

Thanks again

[HansV]

I understand that you wanted to go back from the current month, but you used =TEXT(..., "mmm"). That was incorrect; it caused the formulas to stop working.

You could apply the correct format after transferring data from Access.

[sathya]

Thanks a lot hans. BTW in the formula cell for calculating average in everymonth we using formula as =IFERROR(AVERAGEIFS(Table1[MARKS],Table1[YEAR],YEAR(H1),Table1[MONTH],MONTH(H1)),""). Is there is any formula for calculating average per week as well?

Thanks a lot

[HansV]

You can do that by adding a column for the week number. See the attached version.

auto generator.xlsx

[sathya]

Wow. Amazing work. Thanks again

[sathya]

Hans,

You created formula for months and weeks. Is there is any formula for daily as well. Like Mon,Tue,Wed...Sun?

Thanks

[HansV]

I added another helper column for the day of the week, and used this for the average formula.

auto generator.xlsx