[Solved] Counting merged cells in Calc
[Solved] Counting merged cells in Calc
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!
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 267 times
Last edited by holmes on Mon Jun 11, 2012 5:24 pm, edited 1 time in total.
OpenOffice 3.4 on Fedora 17
Re: Counting merged cells in calc
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.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Counting merged cells in calc
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.
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
Re: Counting merged cells in calc
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.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!
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.
LibreOffice 7.3.7. 2; Ubuntu 22.04
Re: Counting merged cells in calc
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.
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
- MrProgrammer
- Moderator
- Posts: 4907
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Counting merged cells in calc
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:I am looking for a metric that counts the number of cells in the merged ones
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.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.
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.
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.Villeroy wrote:You must not organize your data entry in cross tables.
- Attachments
-
- 201206101204.ods
- (7.85 KiB) Downloaded 176 times
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Counting merged cells in calc
Thanks MrProgrammer.
Yes, agree with rest of what you said. I will experiment with the conditional formatting options.
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.It's at least as tedious to merge the cells as it is to copy the first task to the remaining cells
Yes, agree with rest of what you said. I will experiment with the conditional formatting options.
OpenOffice 3.4 on Fedora 17
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Counting merged cells in calc
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 262 times
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Counting merged cells in calc
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.holmes wrote:Thanks MrProgrammer.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.It's at least as tedious to merge the cells as it is to copy the first task to the remaining cells
Yes, agree with rest of what you said. I will experiment with the conditional formatting options.
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 255 times
LibreOffice 7.3.7. 2; Ubuntu 22.04
Re: Counting merged cells in calc
@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.
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