Currently Being ModeratedJul 25, 2013 4:10 AM (in response to Henning77)
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?
Currently Being ModeratedJul 25, 2013 5:52 PM (in response to Henning77)
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.
B17 (the first Footer row): =SUM(B)
B18 (second Footer row): =SUM(B)/MAX(C)
C2, and filled down to C16 (last body row):
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.