Apache OpenOffice Community Forum

Hello folks I've two questions!
Let's say you have a table of two column (e.g. month and mm of rain ) but there is a repetition in the first column.
january - 1
january - 0
january - 3
february -0
february - 3
(... ad nauseam )

Is there a way to perform simple operation on every number of the same group? for example "for each group of month with the same name, give me the max of column 2". I've managed to use the "group" function but it seems that it is just for aesthetics. I explored a bit the "pivot table" which seems to be what I need but I really don't understand how it works so I'm not sure if it is the right function.

Second question:

if you have a column with the dates of whatever in the format "dd/mm/yy". Is there a way to make calc go through this column and create 3 new column based on it by extracting day month and year? The goal would be that each item has those 3 new attributes so we can perform task based on it like "now give me every event that happened on January".
Or maybe you can perform that action directly without going through new column?
I suppose you can perform both of my question by using code (if c1=january then... ) but I'd rather not because I'm not comfortable with it.

Thanks in advance for your help!
Have a nice day

For your first question, you could do it by putting the IF into the MAX and setting it as an array formula.
If your table is in columns A and B, then I'd suggest using a helper cell (C1) and putting the month you want to search in there.
Then in D1 put When entering the formula, end it with Ctrl+Shift+Enter

Regarding your second question, look at the DAY, MONTH, and YEAR functions. They are simpler than your first question and documented in the Help file.

Pivot tables (aka "data pilots") are easy.

ok just for people who might stumble on this post:

for the date use the function month() and year() which will get the value from a cell in "date" format

for the pivot table make sure that the right items are placed in the right spot when you are in the pivot-table window. With my exemple you should put the first column in the column part and the data to Analise in the "data" part (obviously but it wasn't for me). then by double clicking on the "data" you can chose one or multiple operation to do on them

(Just for completeness; concerning LibreOffice 5.2 and higher)
https://wiki.documentfoundation.org/Rel ... _functions
See MAXIFS, MINIFS

I created a pivot from range A1:A1001 (one header row, 1000 data rows), with dates and categories in row fields, persons in column fields and the numbers in various data fields (sum, min, max, average). Finally I clicked a date cell in the new pivot table and called menu:Data>Group>Group(F12) and grouped the dates by year and month.

[Tutorial] Ten concepts that every Calc user should know

I don't know anything about your cell values. If you have words like "January" in one column you can use these text fields as row fields or column fields as well. If you have monthly measurements, this will be perfectly OK.
My example breaks down daily measurements to aggregated months plus persons and categories as additional groupings. It would work with hourly measurements too.

You can see that the dates in my first column are day numbers formatted by cell style "MyDate". For instance, =VALUE(A2) returns the true cell value in A2 which is 42375. Day #42375 formatted with some date format shows that day #42375 is the 6th of January 2016.
Cell formatting is not important. Cell values matter. OK, the last step (grouping by year and month) requires that numeric cells have some date format. Otherwise the dialog would offer to group "normal" numbers by intervals (1-10, 11-20, 21-30,...).
You may notice that there is no specific order of rows. I created those values from random numbers.