[Solved] Operation on group

Discuss the spreadsheet application

[Solved] Operation on group

Postby Carter7Gindenv » Thu Apr 18, 2019 11:32 am

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
Carter7Gindenv
 
Posts: 4
Joined: Thu Nov 15, 2018 3:32 pm

Re: operation on group

Postby RusselB » Thu Apr 18, 2019 1:50 pm

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   Expand viewCollapse view
=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 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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
RusselB
Moderator
 
Posts: 5414
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: operation on group

Postby Villeroy » Thu Apr 18, 2019 4:44 pm

Pivot tables (aka "data pilots") are easy.
Attachments
Pivot_Month_Person_Category.ods
(64.5 KiB) Downloaded 21 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.x
User avatar
Villeroy
Volunteer
 
Posts: 27112
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [solved]Operation on group

Postby Carter7Gindenv » Fri Apr 19, 2019 11:34 am

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
Carter7Gindenv
 
Posts: 4
Joined: Thu Nov 15, 2018 3:32 pm

Re: [solved]Operation on group

Postby Lupp » Fri Apr 19, 2019 11:45 am

(Just for completeness; concerning LibreOffice 5.2 and higher)
https://wiki.documentfoundation.org/Rel ... _functions
See MAXIFS, MINIFS
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [solved]Operation on group

Postby Villeroy » Fri Apr 19, 2019 12:14 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27112
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

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