[Solved] Associating values to colors

[iddqd]

Hi everyone,

i have a question which i couldn't find an answer to it. perhaps i couldn't phrase it properly to find an answer to it.

I created a OO Calc spreadsheet to track my daily habits such as wake up time, how i felt that morning, my weight, morning exercise, gym and etc.
i have been keep recording my routine for the pas 21 days. i color the cells to rate that particular habit. for example i color the cell to RED if i wasn't happy with the action i have taken with it. let's say i skipped going to gym that day. so i color that cell into RED. if did something good about a particular subject then the color is GREEN. this helps me visualise the day's actions so it constantly reminding me to get better on that. but what i need to do automatically is to rate the day. let's say i have had 5 RED cells out of 10 cells in that row, so the success rating for that they can be %50 with an example of each cells valued 10 which is equal to %10

so i want to create a column at the end of the data which COUNTS THE COLORS and shows me the total value of the colors. i hope i am making some sense.

please have a look at the image i attached to understand me better.

any help are appreciated.

[Lupp]

We had lots of cases where questioners wrote about similar ideas and failed with an attempted evaluation.
The one relavant statement by experienced users always was:
Don't misuse attributes (of cells or of textual content) if you intend to evaluete the contained information as data.
Always reperesent data by cell contents. (Do it generally the "atomic" way. Avoid "compounds".)
Of course, you may use colors (e.g.) to "catch the eye" or to prepare something for "prettyprint".
Don't try to use attribuets beyond this. There are tons of good reasons.

===EDITING a bit later:===
One ot the mentioned threads was https://forum.openoffice.org/en/forum/v ... hp?t=95058.
There you also find an example .ods of mine. It should help to understand the underlying problems.
Unfortunately it can only run if execution of document macros is permitted (macro security "medium") or if the contained user code is moved to a module of the local Standard library for Basic code.
You may use one of the contained functions as a helper for the conversion of information badly repesented by cell background colors to real data.
The relevant limitations:
1. You can't evaluate colors that are shown based on Conditional Formatting this way.
(No serious problem since you can base everythong directly on the used conditions.)
2. Colors looking the same for (one pair of) human eyes may be different in the color space (RGB) used by the software.
(Color NAMES used for palettes aren't specified in relation to RGB representation. RED may mean something different in a different context.
Naming in a given context may change without notion.)
===END EDIT===

[RoryOF]

If you applied the colour to a cell as conditional formatting derived from (say) a ranking in another cell, all becomes relatively easy, as you can use that ranking value to aggregate your results.

But to aggregate results based on a colour you assigned to a cell "because you felt like it" is a much more difficult process.

[Hagar Delest]

You could have 2 rows per day and put in the first row a number (1 or nothing, or allowing intermediate values) for each "habit" of the 2nd row.
Thus the rating would be made in the first row, the calculation being in the last one (either a sum or a ratio, up to you). With a color code as conditional formatting that would apply on both the rated cell and its description in the 2nd row.

Quite cumbersome (not aesthetic too) but no better idea so far.

[iddqd]

thanks to everyone for taking part in the replies. i think i will change the contents into numeric values so that would be much easier solution as recommended to me.

I am now working on to change the color of the cell when entered a specific value.

Thanks everyone