[Solved] Accumulating time and graphing by week

[djryanash]

Hi all,

I've got a number of swim times that I want to graph by week. On average there are 3 entries per week. I can display the week number using WEEKNUM and use an IF statement to accumulate the numbers if they belong to the same week, but I only want to display the totals on the graph of each week, not all figures - so the graph doesn't keep dropping to the lowest amount for each particular week. The graph should only display the SUM of all times for the week.

See attachment for more details.

Thanks for any help you can give me.

Kind regards,

Ryan

[keme]

Quick and dirty, change the formula. D2:

Code: Select all

=SUMIF($C$2:$C$19;C2;$B$2:$B$19)

Copy that down the column. You still get multiple data points for each week, but now they are on top of each other so on the graph it will look like one per week.

[djryanash]

Well played fella. Thanks a lot. Gonna research SUMIF now - so I can figure it out for myself.

Thanks for the help.

[djryanash]

What does the "$" sign do in the equation?

I found an explanation at the OpenOffice Wiki, but it's not very helpful

[djryanash]

Would i be able to do the same thing, but instead of adding the values per week, average them? I see that the is no AVERAGEIF. For that matter, would I be able to MIN and MAX too?

[djryanash]

Would i be able to do the same thing, but instead of adding the values per week, average them? I see that the is no AVERAGEIF. For that matter, would I be able to MIN and MAX too?

Cool. I figured out how to do the averaging with COUNTIF and dividing the SUMIF by the COUNTIF result. Yippee

[keme]

What does the "$" sign do in the equation?

I found an explanation at the OpenOffice Wiki, but it's not very helpful

"$" makes the cell references "absolute", so when you copy the formula to a new cell, it will use the same compare and sum ranges.
The reference to lookup value for sumif (...C2... in the formula above) does not use $, so it is a "relative" reference. This means that it changes when the formula is copied, so in each copy it points to the corresponding week number in the row.

[robleyd]

If you are new(ish) to spreadsheets you may find this helpful:

[Tutorial] Ten concepts that every Calc user should know

[Villeroy]

Durations should be formatted as [HH]:MM:SS rather than HH:MM:SS

How to get the sums of durations per week:
Select the first 3 columns of your table A1:C19
Data>Pivot Table>Create...
[x] From selection
in the next dialog
- drag DATE into [Row Fields]
- drag WEEK NUMBER into [Row Fields]
- drag DURATION into [Data Fields]
[OK]
I recommend to choose a separate sheet as target under [More Options]
You get a new table which is not yet finished.
Click any cell in the date column and call Data>Group&Outline>Group [F12]
Select "Year" only.
[OK]
Now you have a table which sums up the durations for each combination of week and year.
The table can be updated with right-click>Refresh
Proper list keeping in spreadsheets requires that you insert new cells for new new records. This way all references are up to date with the actual list range.