[Solved] Counting merged cells in Calc

Discuss the spreadsheet application

[Solved] Counting merged cells in Calc

Postby holmes » Sun Jun 10, 2012 12:38 pm

I am using a simple time tracker for tasks in calc (openoffice 3.4). The task names are entered from a list, and the cells are merged for continuous durations. In order to calculate the total time per task, I need to know a count of rows spanned by each task. A simple COUNTIF() would only return the number of occurrences of the task string, whereas I am looking for a metric that counts the number of cells in the merged ones. (Attachment, with simple example)

I searched a lot, but cannot figure out how to implement this. I know it is not advised to use merged cells for calculations etc. but having to enter text for every entry makes it more tedious. Hence the merged cells.

Thank you for any input!
Attachments
timetracker.ods
(10.21 KiB) Downloaded 181 times
Last edited by holmes on Mon Jun 11, 2012 5:24 pm, edited 1 time in total.
OpenOffice 3.4 on Fedora 17
holmes
 
Posts: 5
Joined: Sun Jun 10, 2012 12:32 pm

Re: Counting merged cells in calc

Postby Villeroy » Sun Jun 10, 2012 4:09 pm

Spreadsheets do not work like this.

You must not organize your data entry in cross tables. You may do so on paper sheets. With a computer you give up all advantages of your software tools. Simple flat tables, so called "normalized tables", are much easier to maintain and there is plenty of software to analyze normalized tables. Calc provides dozends of formulas, wizards and dialogs to deal with normalized data.

Never overload any formatting attribute with meaning. Particularly you must not merge any cells. Simply abstain completely from this "feature".

Call me arrogant, but this is the one and only way how to keep data in a spreadsheet: http://www.mediafire.com/file/9qzzfqgfp ... _Pilot.ods
Insert rows, delete rows, edit rows, select repetetive data from other lists, filter the list, sort the list, get aggregated results in pivot tables.
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: 27373
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Counting merged cells in calc

Postby holmes » Sun Jun 10, 2012 5:08 pm

Villeroy, Thank you for the reply!

Yes, I had read about avoiding merging etc. which is why I put that in the question itself. Imagine, you have lots of 'Category' columns in the sheet that you shared. And also assume several category names are the same on adjacent days. - Just like several day columns in my sheet, and the same task done over several hours. The whole sheet becomes quite unreadable in my experience. Whereas if I group them (like as in merge and have only one string for the whole group), it is easy to go through.

The merging which I want to do is not just for aesthetic reasons, but it is tedious otherwise. If I have the same task performed for continuous hours, for the whole day - will end up repetitively entering the same thing over and again if I don't do merging.

I perfectly understand what you've meant in your post. But I think it is a tradeoff between simpler operations and the usability.

I know this should be possible with macros (perhaps XMergeableCell, getRowSpan(), isMerged() etc. - never tried it myself). But was looking for a simpler solution.
OpenOffice 3.4 on Fedora 17
holmes
 
Posts: 5
Joined: Sun Jun 10, 2012 12:32 pm

Re: Counting merged cells in calc

Postby jrkrideau » Sun Jun 10, 2012 5:14 pm

holmes wrote:I am using a simple time tracker for tasks in calc (openoffice 3.4). The task names are entered from a list, and the cells are merged for continuous durations. In order to calculate the total time per task, I need to know a count of rows spanned by each task. A simple COUNTIF() would only return the number of occurrences of the task string, whereas I am looking for a metric that counts the number of cells in the merged ones. (Attachment, with simple example)

I searched a lot, but cannot figure out how to implement this. I know it is not advised to use merged cells for calculations etc. but having to enter text for every entry makes it more tedious. Hence the merged cells.

Thank you for any input!


It sounds to me as if you are using the spreadsheet as a gantt chart. I've done so myself and it is a terriblly awkward way to do things. I'd recomment looking into some project management software. IIRC http://www.ganttproject.biz/ is not to difficult to use and might save you a lot of time and effort.

I don't use Base so does anyone know if it does Gantt Charts?

@ Villeroy
Well yes you may be arrogant but you are completely correct here.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3715
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Counting merged cells in calc

Postby holmes » Sun Jun 10, 2012 6:24 pm

jrkrideau, Thanks. I will look into some task management s/w.
The one reason I havn't had done that is they won't be very flexible like a spreadsheet.
OpenOffice 3.4 on Fedora 17
holmes
 
Posts: 5
Joined: Sun Jun 10, 2012 12:32 pm

Re: Counting merged cells in calc

Postby MrProgrammer » Sun Jun 10, 2012 7:19 pm

holmes wrote:I am looking for a metric that counts the number of cells in the merged ones
This is not possible with built-in Calc functions. Read section 9. Using functions and cell ranges in Ten concepts that every Calc user should know where it says "Calc's functions and formulas operate on cell values, so do not expect to perform calculations based on cell formats …". Cell merging is formatting, and although it can be attractive, cell merging often makes spreadsheets difficult to use.

holmes wrote:The merging which I want to do is not just for aesthetic reasons, but it is tedious otherwise. If I have the same task performed for continuous hours, for the whole day - will end up repetitively entering the same thing over and again if I don't do merging.
It's at least as tedious to merge the cells as it is to copy the first task to the remaining cells since all you have to do is Ctrl+drag the Fill handle of the first cell (⌘Drag on a Mac). I entered A2 then dragged that to A3:A7, entered A8, dragged it to A9:A11, entered A12, and dragged to A13:A17.

If you don't want to see the repeated entries, use Format > Conditional Formatting to suppress their display in the following cells (number Format Code ;;;). Or you could put a ditto mark in their place. In the example, I used Format > Style Catalog to create a Ditto style where the number Format Code is General;General;General;\" and then used conditional format formula "cell = previous cell" to apply that style. If you don't understand the "cell = previous cell" concept read about relative references in section 8. Using formulas and cell references.

Villeroy wrote:You must not organize your data entry in cross tables.
Yes. It's better to enter data in normal form and then use Data Pilot to build cross tables, expecially since Calc provides no simple way to do the reverse (Cross table → Normal Form) and because it can be difficult to use functions to analyze data in a cross table.
Attachments
201206101204.ods
(7.85 KiB) Downloaded 112 times
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Counting merged cells in calc

Postby holmes » Sun Jun 10, 2012 8:24 pm

Thanks MrProgrammer.
It's at least as tedious to merge the cells as it is to copy the first task to the remaining cells

Well, not exactly. I have keyboard shortcuts to merge and split, so isn't that of a problem. But ctrl+drag option is okay as well. What I wanted was a calendar-like (where we drag and create an entry) intuitive interface, at the same time do calc operations which I wish to.

Yes, agree with rest of what you said. I will experiment with the conditional formatting options.
OpenOffice 3.4 on Fedora 17
holmes
 
Posts: 5
Joined: Sun Jun 10, 2012 12:32 pm

Re: Counting merged cells in calc

Postby JohnSUN-Pensioner » Mon Jun 11, 2012 10:56 am

In your example tasks are not defined for lines 7, 12 and 15. What does this mean? Maybe it makes sense to fill it special value "No tasks"?
Attachments
timetracker_with_helper_column.ods
One of several solutions
(10.7 KiB) Downloaded 168 times
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Counting merged cells in calc

Postby jrkrideau » Mon Jun 11, 2012 4:29 pm

holmes wrote:Thanks MrProgrammer.
It's at least as tedious to merge the cells as it is to copy the first task to the remaining cells

Well, not exactly. I have keyboard shortcuts to merge and split, so isn't that of a problem. But ctrl+drag option is okay as well. What I wanted was a calendar-like (where we drag and create an entry) intuitive interface, at the same time do calc operations which I wish to.

Yes, agree with rest of what you said. I will experiment with the conditional formatting options.


It looks to me like you could do something like the colour coding in the attached file. As long as you have standard time units you just put the task numbers in each cell and format. This gives you the desired visual layout and you can get times by using a set of countif() functions.

I am sure that the layout and formatting is very sub-optimal but it gives an idea of what I am talking about.
Attachments
taskgant..ods
(11.36 KiB) Downloaded 182 times
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3715
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Counting merged cells in calc

Postby holmes » Mon Jun 11, 2012 5:21 pm

@JohnSUN-Pensioner. Wow! That was clever. Never thought about that.. Thanks a bunch!
Those empty cells in my sheet were idle times. I see that your logic requires every cell to be filled in. Still okay with me. Quite nice!

@jrkrideau, The color coding is nice too. Problem is that I have plenty of tasks (15-20) and more get added and removed. Having to color format from a palette is a bit of work. On the other hand, I already have cell coloring as a conditional format which works very well with merged cells.

Thanks a lot to all the people! I will mark this as solved.
OpenOffice 3.4 on Fedora 17
holmes
 
Posts: 5
Joined: Sun Jun 10, 2012 12:32 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot], MSN [Bot] and 13 guests