How do I calculate a daily average in Excel? > updated?

Answers

1. AntMo

   On 2006-08-14 13:57:30

   
   
   PS if you highlight the full columns before doing the subtotal (rather than the range of data) then this solution will expand to fit different sizes of data without any amendment PPS you might want to use the menu option Data->Sort first of all to get the data into date sequence, just to be on the safe side

2. Lewiy

   On 2006-08-11 11:03:30

   
   
   Highlight all of the data (including the headings), click on Data/PivotChart and PivotTable. When the window opens up, just click on next/ok to skip through the options as the default will be fine. When you return to the worksheet, you will have a new sheet with a blank box, and a small box with your headings in it. Now drag the word date from the little box to the left hand vertical column in the big box and drag the word amount into the big main box. This has now given you a list of dates down the side and the total amounts for each date next to it. You can now just do a simple average function at the bottom of this list.

3. Diva_honey

   On 2006-08-11 05:50:03

   
   
   To work out an average, it is easier if the dates only have one number beside them for example 10/08/06 is 30. You could type this in the column beside your data. After you have entered the numbers as 30, 300, 30 you will need to enter the formula =AVERAGE (cell number:Cell number) putting the cell reference for the 10/08/06 in the first space and then the cell reference for the 12/08/06 in the 2nd space after the : Hope this helps

4. twokay75

   On 2006-08-11 05:40:26

   
   
   Divide the entries into separate columns: 1 column for each day, and allow a number of lines for different entries for any one day (for instance your example shows 2 entries of 11/08/06, so you'll need at least 2 lines for every day if you want to be consistent). Between the "day" columns, have one column that has the "AVERAGE" formula in it, to calculate the average for that particular day. For example, next to the column for Friday 11/08/06, have a daily average column that refers to all the entries for that day. To enter an "AVERAGE" formula, click on the function icon next to the cell entry window just above the table display (the icon looks like "fx"), select AVERAGE and highlight the cells from which the entries to be averaged are. Copy the formula for every "daily average" column in the week. At the end of the set of "daily average" columns, you may want to have a weekly average count. Simply create a column after the last daily average column for that week, use the AVERAGE formula tool and press control while selecting the daily average entries in that week. And voila! You have a weekly average. It may be useful to design this spreadsheet on a weekly basis, and then just copy down the whole thing for the following week. At the end of it, you may even want to have a monthly average.

5. phill281

   On 2006-08-11 05:42:01

   
   
   10+20=30 =average(c1:c3)

6. qwertykph

   On 2006-08-11 05:45:15

   
   
   David, I assume you only want the daily average to appear at the point when the date changes. So here's what I did: Column A: Date 08Oct2006 08Oct2006 08Oct2006 08Oct2006 08Oct2006 08Oct2006 08Oct2006 08Oct2006 08Nov2006 08Nov2006 08Nov2006 08Nov2006 08Nov2006 08Nov2006 08Nov2006 08Nov2006 08Dec2006 08Dec2006 08Dec2006 08Dec2006 Column B Amount 10 20 10 20 10 20 10 20 100 200 100 200 100 200 100 200 30 30 30 30 Column C Daily Average Cell C2 has this formula: =IF(AND (A2=A1, A2<>A3), SUMIF(A:A, A2, B:B) /COUNTIF(A:A, A2), "") Simply copy down formula and then the average will appear only at the points where date changes. PREQUISITE: Data sorted by date in ascending order. Happy averaging! Regards, QwertyKPH @ Yahoo