[Solved] Massaging time-linked statistics into a table

Discuss the spreadsheet application

[Solved] Massaging time-linked statistics into a table

Postby Doug » Sun Jun 22, 2008 2:12 pm

Hello friends, I am a very new user of Openoffice Calc and spreadsheets in general. I have done my research on this question but cannot figure out a solution. I have found it pretty tricky. Hopefully one of the more learned forum people can enlighten me!

Basically I am regularly sent a spreadsheet with two long columns of data. Column A has a time value (HH:MM). Each row of column B contains a number relating to the time next to it in column A. Each column can have up to 2000 values under it - it varies every week.

I want to make a table which creates an average of the values in column B, broken down by hour, based on the time values in column A. Something like this:

Time Range (From Col A)............Average (From Col B)
07:00 to 07:59..............................xxx
08:00 to 08:59..............................yyy
09:00 to 09:59..............................zzz

To make things even more complicated, I want to ignore all values in column B that are negative! (I said it was tricky...)

It there a formula that will help me do this?

I don't know if this is a very clear explanation - it is hard to put in to words. Please let me know if more explanation is required. If someone can help me, this will make my life a lot easier as currently I am doing this hooplah manually.

Regards, Doug
Last edited by Doug on Mon Jun 23, 2008 10:58 am, edited 1 time in total.
Doug
 
Posts: 3
Joined: Sun Jun 22, 2008 2:30 am

Re: Massaging time-linked statistics into a table

Postby Villeroy » Sun Jun 22, 2008 2:28 pm

Select the range in question (or entire columns A and B).
Menu:Data>Data Pilot>Start...
[More Options...]
Make shure that "Results" points to the top-left cell of an unused range, such as C1 if B is the last used column or A1 of another sheet.
Check all the other options below. They won't hurt.
Drag "Time Range" to "Row Fields" and the values to "Data Fields"
Double-click the data field and change function "Sum" to "Average"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28846
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Massaging time-linked statistics into a table

Postby Doug » Sun Jun 22, 2008 2:57 pm

Thank you for your helpful and very quick reply.

However while much appreciated this does meet my desires entirely, as it does not seem to ignore negative values and also seems to calculate a separate average for every time value that is in column A. I want to force the data into blocks, e.g. we might have data at 7.10am, 7.14am and 7.45am, and I want this to be considered all part of a block which I might label "07:00 to 07:59" with a single average for any corresponding values in that time range.

Sorry, it is hard for me to make sense of the situation in words, I think I will go to bed now!

With kind regards and many thanks
Doug
OOo 2.4.X on Ms Windows XP + Knoppix
Doug
 
Posts: 3
Joined: Sun Jun 22, 2008 2:30 am

Re: Massaging time-linked statistics into a table

Postby Villeroy » Sun Jun 22, 2008 3:15 pm

You can filter out negative values. Just hit the filter button on top of the pilot and filter by "Value >= 0". Then select any one of the time values, hit F12 and group by hours.

If it has to be the exact label "HH:00 to HH:59" add a new column to your source data:
C2: =TEXT($A2;"HH")&":00 to "&TEXT($A2;"HH")&":59"
and use that one as row field of your pilot.

 Edit: Attached my test sheet 
Attachments
time_pilot.ods
Test data with 2 pilots to show averages by hours
(24.02 KiB) Downloaded 105 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28846
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Massaging time-linked statistics into a table

Postby Doug » Mon Jun 23, 2008 10:57 am

Outstanding, problem solved! Thank you!!
OOo 2.4.X on Ms Windows XP + Knoppix
Doug
 
Posts: 3
Joined: Sun Jun 22, 2008 2:30 am


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 24 guests