[Solved] Operation on group

Discuss the spreadsheet application
Post Reply
Carter7Gindenv
Posts: 8
Joined: Thu Nov 15, 2018 3:32 pm

[Solved] Operation on group

Post by Carter7Gindenv »

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
Last edited by RoryOF on Fri Apr 19, 2019 12:08 pm, edited 2 times in total.
Reason: Added green tick [RoryOF, Moderator]
openoffice 4.1.4 win7
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: operation on group

Post by RusselB »

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

Code: Select all

=max(if(A1=$c$1;B1;0))
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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: operation on group

Post by Villeroy »

Pivot tables (aka "data pilots") are easy.
Attachments
Pivot_Month_Person_Category.ods
(64.5 KiB) Downloaded 366 times
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
Carter7Gindenv
Posts: 8
Joined: Thu Nov 15, 2018 3:32 pm

Re: [solved]Operation on group

Post by Carter7Gindenv »

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
openoffice 4.1.4 win7
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [solved]Operation on group

Post by Lupp »

(Just for completeness; concerning LibreOffice 5.2 and higher)
https://wiki.documentfoundation.org/Rel ... _functions
See MAXIFS, MINIFS
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [solved]Operation on group

Post by Villeroy »

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.
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
Post Reply