Excel Graph background to be coloured according to value

[pboltonchina]

Hi All,
It's a long time since I've been on here But I always got great help and solutions, so here I go again.
i have a column of numbers in a sheet that vary between 4 and 14 and I have a second sheet that plots a graph from those numbers. the first sheet numbers cells are conditionally formatte to be one colour for values 4 to 7, a different colour for values between 7.1 and 10 and a third colour for a value above 10. I would like to reflect those colours in the graph, either by changing column colour or just having bands of colour across the graph background.
Thanks for reading this post, and as always, great respect to everyone and anyone who could provide a solution.
Regards
Dadouza

 

[Gerald Higgins]

Hi - if you want the chart columns to be different colours, based on their values, this is fairly easy to do.
Set up a separate range to split your data into columns, based on value, and then use THOSE columns as separate sources for different chart data ranges.

For example
Source data.........Chart Data Series 1...........Chart Data Series 2
1..............................1................................................0
2..............................2................................................0
3..............................3................................................0
4..............................0................................................4
5..............................0................................................5

The above is a simplified version - splits your data into two separate columns - values of 3 or less are in Chart Data Series 1, which you can colour blue.
Values above 3 are in Chart Data Series 2, which you can colour red.
And so on.

 

[pboltonchina]

Hi gerald,
Thanks for that, I've split my data and now show the colours in a column graph, However I still want to also maintain my line graph that I originally did which gets it data from 1 column, not 3. So I have 2 data sheets, one for each graph. What I would now like to do is automatically fill the second data sheet when I input on the first. So I fill in data in column H on the sheet named "Data", if that value is between 4 & 7 I want that copied to the sheet named "Data2" in the same Column, i.e. H26=H26, but if the value I enter is between 7.1 & 10 I want to copy H26 into G26 and if the value I enter is above 10.1 then I want H26 to be copied into J26. i would also like this to happen automatically on entering the data in the "Data" sheet. I appreciate that this may be a big ask, but as always, much respect and gratitude comes with this request.
Reagrds to everyone good enough to look at my post.
Dadouza

 

[Gerald Higgins]

I'm not 100% sure about your set up here, but it looks as if a simple IF statement could do this.

On sheet "Data2" in H26, maybe something like . . .
=if(Data!H26<=7,H26,0)

And in G26 . . .
=if(and(Data!H26>7,Data!H26<=10),H26,0)

and so on.

If having a zero at the end of this formula gives you undesired results in your chart, try replacing 0 with ""

Edited to add - I left a closing bracket out of the G26 formula.

 

[pboltonchina]

Hi Gerald, Thanks for that, I had to tweak a bit from
=if(Data!H26<=7,H26,0)
to
=if(Data!H26<=7,Data!H26,0)
and so on, but now doing exactly what I wanted it to do. once again this site and ut's wonderful members has come up trumps.
thank you
Regards
Dadouza

 

[pboltonchina]

Hello again,
I,ve now generated a duplicate workbook and am using the following formulas in 3 consecutive cells on sheet 'Data2'
=IF(Data!H5<=7,Data!H5,0)
=IF(AND(Data!H5>7,Data!H5<=10),H5,0)
=IF(Data!H5>10,Data!H5,0)
this reads a number in sheet 'Data' cell H5 and seperates it into a different cell depending on values : less than 7, between 7 and 10 and above 10. the first and last formulas work fine but the middle one doesn't want to play and I don't know why. It works perfectly well in the original book.
As always, any help greatly appreciated.
Regards
Paul

 

[pboltonchina]

Hi Guys,
All sorted now
Replaced
=IF(AND(Data!H5>7,Data!H5<=10),H5,0)
with
=IF(AND(Data!H5>7.1,Data!H5<=10),Data!H5,0)