Averaging by hours and minutes
Averaging by hours and minutes
Good day!
I have data with date and time like this:
Time f1 f2
24-08-2022 10:19:00 436 369
24-08-2022 10:19:01 471 396
24-08-2022 10:19:02 442 386
24-08-2022 10:19:03 439 422
24-08-2022 10:19:04 436 377
24-08-2022 10:19:05 445 377
24-08-2022 10:19:06 440 413
24-08-2022 10:19:07 450 390
24-08-2022 10:19:08 440 416
24-08-2022 10:19:09 453 417
24-08-2022 10:19:10 491 417
I need a macro to calculate an average f1 and f2 for every hour, or every 5 minutes.
Could you, please, help me to solve this problem?
I have data with date and time like this:
Time f1 f2
24-08-2022 10:19:00 436 369
24-08-2022 10:19:01 471 396
24-08-2022 10:19:02 442 386
24-08-2022 10:19:03 439 422
24-08-2022 10:19:04 436 377
24-08-2022 10:19:05 445 377
24-08-2022 10:19:06 440 413
24-08-2022 10:19:07 450 390
24-08-2022 10:19:08 440 416
24-08-2022 10:19:09 453 417
24-08-2022 10:19:10 491 417
I need a macro to calculate an average f1 and f2 for every hour, or every 5 minutes.
Could you, please, help me to solve this problem?
Last edited by MrProgrammer on Tue Oct 04, 2022 10:27 pm, edited 1 time in total.
Reason: Moved from Calc forum to Macros and UNO API since wants a macro instead of using Calc features
Reason: Moved from Calc forum to Macros and UNO API since wants a macro instead of using Calc features
OpenOffice 3.1 on Windows Vista
Re: Averaging by hours and minutes
No, you need no Makro
see attached example
see attached example
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: Averaging by hours and minutes
karolus, thank you for a reply.
But I have to write a program, that averages data and then uses obtained results on the next steps.
Thank you for an idea with pivot tables. Now I'm trying to make it in macro..
Maybe someone will be faster than me in making code for this task..)
But I have to write a program, that averages data and then uses obtained results on the next steps.
Thank you for an idea with pivot tables. Now I'm trying to make it in macro..
Maybe someone will be faster than me in making code for this task..)
OpenOffice 3.1 on Windows Vista
Re: Averaging by hours and minutes
If you "have to write a program...", that suggests your problem is a homework task, which the Forum will not do for you; although it will help with specific problems in your attempt.
Information on OpenOffice's date/time handling is at
https://wiki.openoffice.org/wiki/Docume ... _functions
which information should put you on the correct track to start solving your problem.
Information on OpenOffice's date/time handling is at
https://wiki.openoffice.org/wiki/Docume ... _functions
which information should put you on the correct track to start solving your problem.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Averaging by hours and minutes
I have a problem with programming pivot tables
When I make them handly - it's allright - see pic1 below:
but when I make in by macro - there is another pic2:
The code is next:
How should I change this code - to make a result like pic1?
When I make them handly - it's allright - see pic1 below:
but when I make in by macro - there is another pic2:
The code is next:
Code: Select all
Sub CreateDataPilotTable()
Dim oSheet
Dim oRange
Dim oRangeAddress
Dim oTables
Dim oTDescriptor
Dim oFields
Dim oField
Dim oCellAddress As New com.sun.star.table.CellAddress
oRange = ThisComponent.Sheets.GetByName("Sheet1").getCellRangeByPosition(0,0,2,22477)
oRangeAddress = oRange.getRangeAddress()
oCellAddress.Sheet = ThisComponent.Sheets.GetByName("Sheet2").Name
oCellAddress.Column = 0
oCellAddress.Row = 0
oSheet = ThisComponent.Sheets.getByName("Sheet2")
oTables = oSheet.getDataPilotTables()
oTDescriptor = oTables.createDataPilotDescriptor()
oTDescriptor.setSourceRange(oRangeAddress)
oFields = oTDescriptor.getDataPilotFields()
oField = oFields.getByIndex(0)
oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
' oField=oFields.getByIndex(1)
' oField.Orientation=com.sun.star.sheet.DataPilotFieldOrientation.COLUMN
oField=oFields.getByIndex(1)
oField.Orientation=com.sun.star.sheet.DataPilotFieldOrientation.DATA
oField.Function=com.sun.star.sheet.GeneralFunction.AVERAGE
oField=oFields.getByIndex(2)
oField.Orientation=com.sun.star.sheet.DataPilotFieldOrientation.DATA
oField.Function=com.sun.star.sheet.GeneralFunction.AVERAGE
oTables.insertNewByName("MyFirstDataPilot3", ThisComponent.Sheets.getByName("Sheet2").getCellByPosition(0,0).getCellAddress(), oTDescriptor)
End Sub
- Attachments
-
- pic2.png (20.23 KiB) Viewed 1648 times
-
- pic1.png (13.77 KiB) Viewed 1648 times
OpenOffice 3.1 on Windows Vista
Re: Averaging by hours and minutes
I've got it
from this branch
https://forum.openoffice.org/en/forum/v ... ot#p422996
Code: Select all
oField = oTableDescr.getDataPilotFields().getByName("Data")
oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.COLUMN
https://forum.openoffice.org/en/forum/v ... ot#p422996
OpenOffice 3.1 on Windows Vista
Re: Averaging by hours and minutes
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Averaging by hours and minutes
Villeroy, thank you for a help
But I'm stuck with grouping my pivot table by hours and minutes in macro..
can't use spreadsheetfunctions
Is there any possible macro to repeat this action:
Select cell with date and time in pivot table -> Data -> Group and Outline -> Group - Intervals -> Hours and Minutes?
MacroRecorder gives strange commands with dispatcher - don't understand them..
And when i add this code to my code - the grouping doesn't happen.
But I'm stuck with grouping my pivot table by hours and minutes in macro..
can't use spreadsheetfunctions
Is there any possible macro to repeat this action:
Select cell with date and time in pivot table -> Data -> Group and Outline -> Group - Intervals -> Hours and Minutes?
MacroRecorder gives strange commands with dispatcher - don't understand them..
Code: Select all
oDocumentModel = ThisComponent
oDocumentView = oDocumentModel.getCurrentController()
oDocumentFrame = oDocumentView.Frame
' the dispatcher service is used to send commands from the
' document frame to the underlaying office application
oDispatcher = CreateUnoService("com.sun.star.frame.DispatchHelper")
Dim mArgs1(0) As New com.sun.star.beans.PropertyValue
mArgs1(0).Name = "ToPoint"
mArgs1(0).Value = "$A$13"
oDispatcher.executeDispatch(oDocumentFrame, ".uno:GoToCell" ,"" ,0 ,mArgs1())
OpenOffice 3.1 on Windows Vista
Re: Averaging by hours and minutes
If you can't use spreadsheet functions, why do you use a spreadsheet anyway? Either you write a program or you use a spreadsheet. Most of that macro voodoo on sheets is unproductive nonsense.
Let me guess:
You export csv from a database, import the csv into sheets and then you try to reprogram database functionality?
Let me guess:
You export csv from a database, import the csv into sheets and then you try to reprogram database functionality?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Averaging by hours and minutes
Yes, i import the csv into sheets and then try to aggregate data with selection by 1 min, 5 min or 60 min on user form.
OpenOffice 3.1 on Windows Vista