Is it possible to count by color?

Discuss the spreadsheet application

Is it possible to count by color?

Postby cpc » Wed May 27, 2009 9:07 am

I create a array that consists of one single patterning with different colors, I just wonder if it is possible to use countif to count by color? thanks for reply.
OOo 3.1.X on Ubuntu 8.x
cpc
 
Posts: 6
Joined: Sat May 16, 2009 8:05 am

Re: Is it possible to count by color?

Postby ccornell » Wed May 27, 2009 10:01 am

I'm guessing not. countif acts on cell contents not cell formats
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_COUNTIF_function
http://wiki.services.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide/Calc_functions_similar_to_database_functions

You may want to try asking on the dev@api.documentation.openoffice.org mailing list. Maybe one of the developers there will have a suggestion.
openSUSE 11.4, KDE4.6 with OpenOffice.org 3.3
User avatar
ccornell
Volunteer
 
Posts: 611
Joined: Sun Oct 07, 2007 7:21 am

Re: Is it possible to count by color?

Postby mriosv » Thu Jun 04, 2009 12:51 pm

It's possible with SUM() function in array mode with conditions, and using the CELL() function to get the colour into, the SUM().
http://wiki.services.openoffice.org/wik ... y_formulas
LibreOffice 3.5.4 AOo-3.4 on Win 7 Ultimate
mriosv
Volunteer
 
Posts: 651
Joined: Mon Mar 09, 2009 1:12 am
Location: Galiza (España)

Re: Is it possible to count by color?

Postby Villeroy » Thu Jun 04, 2009 1:13 pm

You can apply colours (and any other attributes) by criteria. This is called "conditional formatting". Then use the same criteria for counting, summing, whatever.
If there are no clear criteria to use, you can set tags, apply conditional formatting by the tags and count the tagged cells.
For instance, you can use column A of some list to put an "X" and then use conditional formatting to highlight the row if it has an "X" in A and use COUNTIF to count all "X" in A. This allows for many other features, such as filter, sort.

I've written some userdefined function to extract hidden information, such as colours. It should be used to repair badly designed spreadsheets: viewtopic.php?f=21&t=2762
Function CELL_BACKCOLOR can pull out the colour code out of a cell. This reveals another problem when hiding information in colours: There are many shades of the same colour.
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: 27365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is it possible to count by color?

Postby cpc » Thu Jun 04, 2009 1:51 pm

Thanks for all the replies, but I still can not get into it, as you can see in the screenshot attached, I would like to count the number of different color respectively, how can I make it?
Attachments
Screenshot.png
Screenshot.png (1.54 KiB) Viewed 32839 times
OOo 3.1.X on Ubuntu 8.x
cpc
 
Posts: 6
Joined: Sat May 16, 2009 8:05 am

Re: Is it possible to count by color?

Postby Villeroy » Thu Jun 04, 2009 2:18 pm

See attached file with conditional formattings in A1:J8. Calc is a spreadsheet and not a game editor.
Attachments
colour_count.ods
(16.52 KiB) Downloaded 2081 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: 27365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is it possible to count by color?

Postby Dave » Thu Jun 04, 2009 2:26 pm

Assuming that you used a special character, then a font colour for that character then the answer is, I think, "No.", since you are looking only for the colour aspect. That is also assuming that you used the same character for each entry, and so can not count the character used. Otherwise, there might be a way to count each character if different for each colour of data entry.

But I've been known to be wrong sometimes in this life.

However... you might try double-dipping. That is, have a separate column beside each visible column, and make an entry in that separate column for each type of character. Red could be "R", Blue "B", and so on. Then you can count each of those aspects using SUMIF(). Hide the unwanted columns. A little more work, but it will do what you need.

EDIT: You could take the second column entry from a conditional IF() statement, or a VLOOKUP column.

David.
Dave
 
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: Is it possible to count by color?

Postby acknak » Thu Jun 04, 2009 11:11 pm

Here's an approach that seems to work fairly well.

It uses two tricks to get the result:

First, it uses a custom number format to display all numbers as a circle symbol. That means that you can enter different numbers to represent different markers: 1 for blue, 3 for red, and 5 for black. That allows you to simply count the number of times a particular number occurs on the board, rather than counting colors.

Second, to get the colors, it uses a conditional format that applies a style which provides the appropriate color. Which style is applied is determined by the value in the cell, from a lookup table.

You can move pieces around by dragging the cells; holding Ctrl while dragging will copy the pieces to make new ones. The counts will update automatically.
Attachments
Game_board_example_v2.ods
(12.24 KiB) Downloaded 2324 times
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Is it possible to count by color?

Postby Villeroy » Fri Jun 05, 2009 12:11 pm

acknak wrote:Second, to get the colors, it uses a conditional format that applies a style which provides the appropriate color. Which style is applied is determined by the value in the cell, from a lookup table.


:shock: I can't believe what I see. :bravo:
You are using the STYLE function in one conditional format to look up one of 3 styles?This combination of STYLE with conditional formatting overcomes the limitation of 3 styles in the most elegant manner.

I managed to add a 4th style "_G" with green color and I removed the ";0" from the VLOOKUP: download/file.php?id=4684

Updated the article on my macro solution: viewtopic.php?f=21&t=6039&p=28159
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: 27365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is it possible to count by color?

Postby Dave » Fri Jun 05, 2009 3:08 pm

acknak wrote:Here's an approach that seems to work fairly well.

It uses two tricks to get the result:


NICE !!!!!!!!!!

Elegant! It's like watching a chess game, and thinking you have it figured out, then see this incredible combination you hadn't seen coming at all.

David.
Dave
 
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: Is it possible to count by color?

Postby acknak » Fri Jun 05, 2009 3:49 pm

Yes, well, that's actually a little embarrassing because, to be honest, I'm not sure that's an approved way to use STYLE. I don't think it always worked like this, but one of the developers suggested it might be one way to allow easy access to more than three styles in a conditional format. I assume that the dev went ahead and made the change at some point--I happened to be fooling around one day and found that it worked. As far as I know, it's not documented anywhere.

See Issue 71392, the comment from fst Fri Nov 10 13:34:36 2006, and the reply immediately following.

Hmm, I see now that I never added a follow-up comment on that issue--done now.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Is it possible to count by color?

Postby mriosv » Sat Jun 06, 2009 5:45 pm

it´s a very nice solution, and I have try to add two more styles and not problem, works well,
LibreOffice 3.5.4 AOo-3.4 on Win 7 Ultimate
mriosv
Volunteer
 
Posts: 651
Joined: Mon Mar 09, 2009 1:12 am
Location: Galiza (España)

Re: Is it possible to count by color?

Postby jimavera » Fri May 28, 2010 1:41 am

WARNING: Using STYLE() in Conditional Formats (like in Game_board_example_v2.ods) causes OpenOffice suck CPU while sitting there "doing nothing". It re-evaluates the formulas over and over, many times per second. Somehow it still responds to user input, but with many such cells CPU utilization can reach 80-90% when "idle".

An ubuntu bug report is at https://bugs.launchpad.net/ubuntu/+source/openoffice.org/+bug/586599

(Seen in oo 3.2 OOO320m12 on amd64)
OOo 3.0.X on Ubuntu 8.x + Windows XP
jimavera
 
Posts: 3
Joined: Thu Feb 26, 2009 1:04 am

Re: Is it possible to count by color?

Postby acknak » Fri May 28, 2010 5:59 pm

Yeah, I've noticed that as well: viewtopic.php?p=136558#p136558

However, I'm not even sure that using STYLE in a conditional formatting formula like this is an "approved" use.

I wonder if a report should be filed on the OOo issue tracker as well?
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Is it possible to count by color?

Postby achillies000 » Tue Aug 02, 2011 6:35 pm

If there are no clear criteria to use, you can set tags, apply conditional formatting by the tags and count the tagged cells.
For instance, you can use column A of some list to put an "X" and then use conditional formatting to highlight the row if it has an "X" in A and use COUNTIF to count all "X" in A. This allows for many other features, such as filter, sort.
OpenOffice 3.1
achillies000
 
Posts: 1
Joined: Tue Aug 02, 2011 6:34 pm

Re: Is it possible to count by color?

Postby kq76 » Sun Feb 12, 2012 12:42 am

I registered solely to thank you for this creative trick. I've wanted to do this for such a long time so to stumble upon it last night was a great treat. Thank you to all who contributed to it and particularly to acknack for attaching the simple to follow game board example! :)
OpenOffice 4.1.1 on Windows 10 (as of November 2015)
kq76
 
Posts: 6
Joined: Sun Feb 12, 2012 12:36 am

Re: Is it possible to count by color?

Postby mgrivas » Sat Mar 10, 2012 8:31 pm

As kq76, I registered also mainly to thank you a lot for your contribution to my thoughts and knowledge. :bravo: :super:
I bet you are programming in C :ugeek: , otherwise it's not very common to use a function in an
Code: Select all   Expand viewCollapse view
if
clause just to trigger the function, irrelevantly of what's on the
Code: Select all   Expand viewCollapse view
then
part.
Thank you again.

By the way, there seems to be no problem any more regarding the CPU-overloaded by re-evaluation of formulas.
I am working with LibreOffice 3.4.x on Linux (several distributions) and my spreadsheet is far more complex than the board.
I have 7 sheets, 3 of which have about 100 lines each on which I change colour to each line regarding the ROW() -for odd and even rows, the respective cell on column A (say teams) and the respective cell on column B (say groups). I have a total of 8 different colours and an exception colour (gray) which is used for empty lines and is placed as a second condition (in Java terms, the Exception), that is a total of 10 styles, including the parent-style that bears common properties.
It is interesting, though, that when I changed the styles, everything went very slow. Then I restart Calc and it goes alright, negligibly slower.
My computer is a 5-year-old notebook, with a P4 core 2 Duo and 2 GB, so it would not stand a CPU burden in today terms.
LibreOffice 3.4.x @ Linux (Mint, Ubuntu, openSuSE)
mgrivas
 
Posts: 4
Joined: Thu Mar 08, 2012 10:07 pm


Return to Calc

Who is online

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