Numbers (or Excel): daily/weekly/monthly average

[chucker]

I have a Numbers spreadsheet with a relatively simple layout; the six columns relevant in this question are Date, Time, Value, Day's Average, Week's Average and Month's Average. I want to fill the latter three automatically with an appropriate formula. I can manually select a day's worth of value cells and build an AVERAGE() call — but I can't just copy that over to the next day, because Numbers doesn't figure out what constitutes a "day". The amount of values per day varies; sometimes one, sometimes none, and sometimes maybe even eight.

So basically, I want to somehow tell it that whenever the Date column doesn't change for several cells in a row, those altogether should be averaged. With Week and Month it gets even trickier; if I happen to leave out a day in the past 7 days, it shouldn't take into account the values from 8 days back.

Any ideas? :D

[Dave]

Can you put the call data in a separate sheet? If so, each day could get a row, and you could take the average of each row to get the daily average. The weekly average would then be the average of each seven rows. Not sure about months though.

[chucker]

Can you put the call data in a separate sheet? If so, each day could get a row, and you could take the average of each row to get the daily average. The weekly average would then be the average of each seven rows. Not sure about months though.

Not sure how that would solve my problem. You're saying I'd have one sheet with all values, and one with the daily averages, right? How would the second one get filled?

[Dave]

Not sure how that would solve my problem. You're saying I'd have one sheet with all values, and one with the daily averages, right? How would the second one get filled?

Right, there's a "data" sheet, and a "summary" sheet. You fill in the data, the computer computes the summary. Hmm... I guess it doesn't have to be in two sheets. If you're using Excel, you can pre-populate the rows with null values for the beginning and ending of each month, then insert new rows for the days of the month between the rows for the beginning and the end. As long as your original formula was "AVERAGE(A1..B1)", not "AVERAGE(A1, B1)", Excel will automatically include the new values.

Don't know if that trick works in Numbers.

[chucker]

Hm, alright — let me illustrate my problem better, this time with a sum.

Demo file: Numbers '09 format (http://localhostr.com/files/d1aeeb/demo.numbers); XLS format (http://localhostr.com/files/cd6ce0/demo.xls)

Defining the formula for one day is straightforward enough:

http://localhostr.com/files/a482c5/Picture+2.png

I have six rows, with the same value for the Date column, but different ones for Time. The Day ∑ column should simply aggregate all More Stuff normal and plus values together where the Date value happens to be the same.

But I can't just copy this formula to the next day. Just dragging it down would give me one value too much, because this day happens to have one value less:

http://localhostr.com/files/bf3084/Picture+4.png

What I want to do is tell Numbers not to sum specific cells, but to find all cells in the columns normal and plus whose Date value are the same.

[Dave]

Hm, alright — let me illustrate my problem better, this time with a sum.

Demo file: Numbers '09 format (http://localhostr.com/files/d1aeeb/demo.numbers); XLS format (http://localhostr.com/files/cd6ce0/demo.xls)

Defining the formula for one day is straightforward enough:

http://localhostr.com/files/a482c5/Picture+2.png

I have six rows, with the same value for the Date column, but different ones for Time. The Day ∑ column should simply aggregate all More Stuff normal and plus values together where the Date value happens to be the same.

But I can't just copy this formula to the next day. Just dragging it down would give me one value too much, because this day happens to have one value less:

http://localhostr.com/files/bf3084/Picture+4.png

What I want to do is tell Numbers not to sum specific cells, but to find all cells in the columns normal and plus whose Date value are the same.

(Sorry, this has gotta be in Excel for me to help... My version of Numbers is too old to open your files)

Open up the Excel file and click on any cell whose row does not contain a date. Go up to the "Insert" menu and click "Rows". Populate that row with data, and the day's sum automatically updates without affecting the sums for the other days. Excel automatically stretches the formula to include the new row. You can't do this on a row that contains a date because the new row goes between the selected row and the row above that. (If you've selected something on row 3, the existing rows >= 3 get pushed down and the inserted row is the new row 3.)

I'm still thinking about an automated way of figuring out how many entries are in each day. And how many days are entered per week (although it seems to me that the easy way out of this is to just enter a day as empty, then the answer is always 7). And how many days per month (perhaps a lookup function).

[Motor]

Are you familiar with the "SUMIF" function? If you fill in the date for all rows, you could try this formula for cell F3: "=SUMIF($B$3:$B$19,B3,$D$3:$D$19)+SUMIF($B$3:$B$19, B3,$E$3:$E$19)"

That will give you the daily sum for that row. Copying the formula down should give the correct results for each row.

You can also divide the result by "COUNTIF(B3:B19,B3)" to get the daily average.

Note: I tried this out in Excel 2003, I'm not sure if it will fit Numbers formula conventions.

[chucker]

Thanks so far, both of you.

Are you familiar with the "SUMIF" function? If you fill in the date for all rows, you could try this formula for cell F3: "=SUMIF($B$3:$B$19,B3,$D$3:$D$19)+SUMIF($B$3:$B$19, B3,$E$3:$E$19)"

Excellent!

This requires, unfortunately, that I fill in the date for each row, which is kind of visually unpleasant (much harder to see now when the day changes). I don't suppose I can somehow define that it automatically adds a bottom border line whenever the date changes? ;)

You can also divide the result by "COUNTIF(B3:B19,B3)" to get the daily average.

:( No, not quite, I'm afraid. With that trick, if the Value column is empty, it will treat that as zero, making the average wrong. This doesn't happen when I call AVERAGE().

Note: I tried this out in Excel 2003, I'm not sure if it will fit Numbers formula conventions.

=SUMIF($B,B8,normal)+SUMIF($B,B8,plus)

works. (I'm not sure why it recognizes the columns "normal" and "plus", but not "Date", though.)

[chucker]

:( No, not quite, I'm afraid. With that trick, if the Value column is empty, it will treat that as zero, making the average wrong. This doesn't happen when I call AVERAGE().

COUNTIFS does the trick here. :)

[bassplayinMacFiend]

Are you familiar with the "SUMIF" function? If you fill in the date for all rows, you could try this formula for cell F3: "=SUMIF($B$3:$B$19,B3,$D$3:$D$19)+SUMIF($B$3:$B$19, B3,$E$3:$E$19)"

That will give you the daily sum for that row. Copying the formula down should give the correct results for each row.

You can also divide the result by "COUNTIF(B3:B19,B3)" to get the daily average.

Note: I tried this out in Excel 2003, I'm not sure if it will fit Numbers formula conventions.

This is basically what I was going to suggest.

[bassplayinMacFiend]

This requires, unfortunately, that I fill in the date for each row, which is kind of visually unpleasant (much harder to see now when the day changes). I don't suppose I can somehow define that it automatically adds a bottom border line whenever the date changes? ;)

Conditional Formatting is your friend. I don't use Numbers, but Excel will allow formulas to trigger conditional formatting. A simple IF(next cell date <> this cell date, add underline, don't add underline) would take two seconds to implement. Make sure your cell references are relative and you can copy the conditional formatting wherever you like.