Weekly averages calculation from daily data
memyselfandi21321
Posted messages
8
Status
Member
-
via55 Posted messages 14387 Registration date Status Member Last intervention -
via55 Posted messages 14387 Registration date Status Member Last intervention -
Hello,
I'm stuck on Excel because I can't create a formula to calculate averages from daily input data.
Let me explain:
I have an Excel table with dates of the year in column A from January 1 to December 31, in column B the week number (1 to 52), and in column C a number that I enter daily.
I would like a formula in column D, for example, to calculate the average per week (week 1, 2...) without me having to do it for each week. A little extra difficulty, there are weeks with 5 days but others with 4 days (public holidays, for example). Therefore, the average should be calculated based on the non-empty cells (in a week where I enter data for 3 days, the average should be based on those 3 days and not on the 7 days).
Do you have any idea of the method to follow considering that I can't use a pivot table?
Thank you in advance for your help.
I'm stuck on Excel because I can't create a formula to calculate averages from daily input data.
Let me explain:
I have an Excel table with dates of the year in column A from January 1 to December 31, in column B the week number (1 to 52), and in column C a number that I enter daily.
I would like a formula in column D, for example, to calculate the average per week (week 1, 2...) without me having to do it for each week. A little extra difficulty, there are weeks with 5 days but others with 4 days (public holidays, for example). Therefore, the average should be calculated based on the non-empty cells (in a week where I enter data for 3 days, the average should be based on those 3 days and not on the 7 days).
Do you have any idea of the method to follow considering that I can't use a pivot table?
Thank you in advance for your help.
4 answers
The formula =AVERAGE() does not take into account empty cells (it does count those with a value of zero). And with the formula =AVERAGEIF() you can set conditions
So, for your averages in column D, use the following formula =AVERAGEIF(A:A,A1,C:C), and drag it down through the entire height.
To make it even better, create a separate table with only 52 rows ^^
--
Remember remember the fifth of November
Gunpowder, treason and plot.
I see no reason why gunpowder, treason
Should ever be forgot...
So, for your averages in column D, use the following formula =AVERAGEIF(A:A,A1,C:C), and drag it down through the entire height.
To make it even better, create a separate table with only 52 rows ^^
--
Remember remember the fifth of November
Gunpowder, treason and plot.
I see no reason why gunpowder, treason
Should ever be forgot...
Hello memy
a possibility (by averaging the last 7 cells)
https://www.cjoint.com/?3GkpjHNFCOD
Best regards
a possibility (by averaging the last 7 cells)
https://www.cjoint.com/?3GkpjHNFCOD
Best regards
Thank you both, I managed to calculate the weekly average by combining your two formulas and also display it only once per week (on the Sunday line for my case).
I’m going to take advantage of your kindness by asking you a new question, still based on the same file. I now want to create a graph with weeks on the x-axis and the different averages I've calculated on the y-axis. When I do this, it shows 1 1 1 1 1 1 1 2 2 2 2 2 2 on the x-axis, whereas I want each week to appear only once as 1 2 3 4 5...
Do you have any idea what can be done to achieve this (once again, without a pivot table unfortunately).
Thank you in advance.
I’m going to take advantage of your kindness by asking you a new question, still based on the same file. I now want to create a graph with weeks on the x-axis and the different averages I've calculated on the y-axis. When I do this, it shows 1 1 1 1 1 1 1 2 2 2 2 2 2 on the x-axis, whereas I want each week to appear only once as 1 2 3 4 5...
Do you have any idea what can be done to achieve this (once again, without a pivot table unfortunately).
Thank you in advance.
I'm sorry again, but I can't manage it Via55 by reproducing your formula.
Here is my file, you'll see that in columns L & M I've tried to retrieve the elements from each week but it indicates #REF!
https://www.cjoint.com/?0GkrvNHyhvv
Here is my file, you'll see that in columns L & M I've tried to retrieve the elements from each week but it indicates #REF!
https://www.cjoint.com/?0GkrvNHyhvv
Hi,
Here's your file with the explanations about why it wasn't working
https://www.cjoint.com/?3Gkr46XTN6N
I think it's fine, but since the results are found by macro and I'm using LibreOffice and not OpenOffice, it can't read them, so I wasn't able to check
Thanks for keeping me updated
Best regards
Here's your file with the explanations about why it wasn't working
https://www.cjoint.com/?3Gkr46XTN6N
I think it's fine, but since the results are found by macro and I'm using LibreOffice and not OpenOffice, it can't read them, so I wasn't able to check
Thanks for keeping me updated
Best regards
Thank you for your quick response. Unfortunately, it doesn't work since it doesn't calculate the average for each week and each line represents a day of the week.
Your request was: I would like a formula in column D, for example, to calculate the average per week... and that's exactly what my formula does.
If you want a summary by week, you need to create a second table as I indicated:
1. On a new sheet, let's call it Sheet2, put your week references (from 1 to 52 vertically) in column A.
2. In column B of this table, write the same formula (the references change a bit because we are no longer on a single sheet): =AVERAGEIF(Sheet1!B:B,A1,Sheet1!D:D)