Hi,
I'm trying to have numbers calculating an average based on the actual day of the year.
I have a table with a lot of data for a lot of days within the year and would like to know the current average per day. I think I have to have one cell that gives me the current day of the year and another with the total amount of all the data I enter in the table and a third cell that gives me the average of it.
What would be the easiest salvation?
Thank you
Assuming first column is headed Date, and second column (B) has numeric values ...
Then you just need one more column, C, which could have in each cell:
=SUM($B$2:$B2) / DUR2DAYS(Date - DATE(YEAR(Date),1,1) )
Note that the first element in the sum has two $ signs, to fix the cell exactly.
The second element has just one $ sign, to fix the column, but leave the row to change as needed.
This creates a sum of that column to the current row.
I couldn't find a built-in formula to give "day of year" so resorted to the formula
DUR2DAYS(Date - DATE(YEAR(Date),1,1)
In other words, subtract the first day of the year from the current date.
Then divide to get the average.
Is that what you are wanting?
Assuming first column is headed Date, and second column (B) has numeric values ...
Then you just need one more column, C, which could have in each cell:
=SUM($B$2:$B2) / DUR2DAYS(Date - DATE(YEAR(Date),1,1) )
Note that the first element in the sum has two $ signs, to fix the cell exactly.
The second element has just one $ sign, to fix the column, but leave the row to change as needed.
This creates a sum of that column to the current row.
I couldn't find a built-in formula to give "day of year" so resorted to the formula
DUR2DAYS(Date - DATE(YEAR(Date),1,1)
In other words, subtract the first day of the year from the current date.
Then divide to get the average.
Is that what you are wanting?
Hi Henning,
My assumptions differ slightly from kharisma's.
I've assumed you want an average based on the days for which there has been activity. For example if this were the average daily sales of a shop open Monday to Friday, there would be no data for Saturdays or Sundays. Similarly, the data table could record the earnings of a casual worker—some days the worker could receive payment from multiple employers, other days he might be unavailable for employment (or remain unhired).
Here's an example. I've made the 'amounts' constant to simplify checking the calculations.
The total and Average per Day calculations have been placed in two Footer rows. this allows using a single letter in the tew formulas to reference wach column, and permits adding rows to the table without revising the formulas.
Formuas:
B17 (the first Footer row): =SUM(B)
B18 (second Footer row): =SUM(B)/MAX(C)
C2, and filled down to C16 (last body row):
=IF(COUNTIF(A$2:A2,A2)=1,MAX($C$1:C1)+1,"")
This presents a running count of the number of distinct dates in Column A. The MAXimum value in the column gives the total number of days recorded, and is used to determine the per day average of the amounts (or other values) in column B.
Column C is a 'working column,' and may be hidden.
Regards,
Barry
Calculate daily average
