Formula to calculate averages by date intervals
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Formula to calculate averages by date intervals
Hi hans and rudi,
Please look at the attachment and any help is much appreciated.
Thanks a lot
sathya
Please look at the attachment and any help is much appreciated.
Thanks a lot
sathya
You do not have the required permissions to view the files attached to this post.
Last edited by Rudi on 14 Mar 2016, 15:32, edited 1 time in total.
Reason: Edited by Rudi to give the post a meaningful title
Reason: Edited by Rudi to give the post a meaningful title
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: formula
Attached...
(Oh...Hans pipped me! Take your pick....)
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: formula
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
Thanks a lot again hans
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: formula
You must just check that the formula references include the extra dates you input...
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: formula
Thanks for your reply hans.
How does formula generate automatically when ever i go to new line?
Thanks
How does formula generate automatically when ever i go to new line?
Thanks
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula
This version has formulas up to row 100. If you need more, you can easily modify them.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: formula
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.
Tables will also update these dynamically if you add new records to the bottom of the table.
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: formula
Hi Hans and Rudi,
Please look at the attachment.
Thanks in advance :)
Please look at the attachment.
Thanks in advance :)
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: formula
Attached...
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: formula
Hahaha
Hans and Rudi u guys are brilliant. Thanks Rudi :)
Hans and Rudi u guys are brilliant. Thanks Rudi :)
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula
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.
Apart from that, I don't understand why you changed the formulas in F1:Q1 to use text...
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: formula
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
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
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula
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.
You could apply the correct format after transferring data from Access.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: formula
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
Thanks a lot
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula
You can do that by adding a column for the week number. See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: formula
Wow. Amazing work. Thanks again
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: formula
Hans,
You created formula for months and weeks. Is there is any formula for daily as well. Like Mon,Tue,Wed...Sun?
Thanks
You created formula for months and weeks. Is there is any formula for daily as well. Like Mon,Tue,Wed...Sun?
Thanks
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: formula
I added another helper column for the day of the week, and used this for the average formula.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans