‽
|
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? |
quote |
Ninja Editor
Join Date: May 2004
Location: Bay Area, CA
|
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.
|
quote |
‽
|
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?
|
quote |
Ninja Editor
Join Date: May 2004
Location: Bay Area, CA
|
Quote:
Don't know if that trick works in Numbers. |
|
quote |
‽
|
Hm, alright — let me illustrate my problem better, this time with a sum.
Demo file: Numbers '09 format; XLS format Defining the formula for one day is straightforward enough: 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: 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. |
quote |
Ninja Editor
Join Date: May 2004
Location: Bay Area, CA
|
Quote:
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). |
|
quote |
Member
Join Date: Feb 2006
|
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. |
quote |
‽
|
Thanks so far, both of you.
Quote:
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? Quote:
Quote:
Code:
=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.) |
|||
quote |
‽
|
|
quote |
Banging the Bottom End
Join Date: Jun 2004
|
Quote:
|
|
quote |
Banging the Bottom End
Join Date: Jun 2004
|
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.
|
quote |
Posting Rules | Navigation |
|
Thread Tools | |
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
What's Your Monthly iPhone Bill? | drewprops | General Discussion | 24 | 2008-05-30 16:37 |
The Daily Coyote | Foj | AppleOutsider | 7 | 2008-01-08 17:06 |
Numbers/Excel countif help | Mac+ | Genius Bar | 0 | 2007-09-10 18:26 |
BBC weekly news quiz | Windswept | AppleOutsider | 27 | 2007-02-04 13:28 |
High GPA - average school , average GPA - good school? | Partial | AppleOutsider | 15 | 2006-12-02 22:47 |