Ask Your Question
0

How to automatically assign different colours to categories in a bar chart [closed]

asked 2014-10-05 03:53:53 +0200

yeehi gravatar image

updated 2014-10-05 04:32:18 +0200

I would like to represent a sequence of categories in bar chart form. I would like the different categories to each have its own colour bar.

The x axis would consist of the sequence, for example:

ACTGAGCTCCAGA

A=Red C=Green T=Blue G=Yellow

How can all of the As be made to appear Red, without having to individually set ever instance of A?

The Y axis is a percentage value, 0-100%.

y values: 385976294480983
x values: ACTGAGCTCCAGA

A C T G A G C T C C A G A
3 8 5 9 7 6 2 9 4 4 8 0 9 8 3

Note:

In the "Data Range" section of the chart wizard, selecting "Data series in columns" results in different coloured columns for every column. In the required instance, 4 colours, no more nore less, are needed, one colour for each of the four values, ACTG.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-04 19:26:22.884543

2 Answers

Sort by » oldest newest most voted
3

answered 2014-10-05 08:14:51 +0200

oweng gravatar image

Here is another way, using the Property Mapping feature introduced in v4.3. It basically uses this type of formula in a third column:

=IF(A2="A",COLOR(255,0,0),IF(A2="C",COLOR(0,255,0),IF(A2="T",COLOR(0,0,255),COLOR(255,255,0))))

Which means:

  • If the X value is "A", create a colour value based on RGB 255,0,0 (i.e., red) ELSE
  • If the X value is "C", create a colour value based on RGB 0,255,0 (i.e., green) ELSE
  • If the X value is "T", create a colour value based on RGB 0,0,255 (i.e., blue) ELSE
  • If the X value is "G", create a colour value based on RGB 255,255,0 (i.e., yellow).

This column is then selected for the Fill Color data range. Sample ODS here.

edit flag offensive delete link more

Comments

That's really cool. I'll pay more attention to the release notes in future.

mikebibo gravatar imagemikebibo ( 2014-10-05 08:34:06 +0200 )edit
2

answered 2014-10-05 06:32:30 +0200

mikebibo gravatar image

Here's one way, by rearranging your data. The rearranging could be done with formulas. C:\fakepath\example_barchart.ods Adjust colours for each category accordingly. Note that I could not be sure of how the string of digits was divided. Set bar overlap to 100% for best visual effect.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-10-05 03:53:53 +0200

Seen: 8,037 times

Last updated: Oct 05 '14