Simple (?) charting question

[Copernicus]

Brain dead this morning!

I want to create a chart that is a series of horizontal bars. The axis label is years. Each bar changes color from Green to Yellow to Red in a specified year based on data in a table. I tried a table with years across the top and data values of 1, 2, 3 in rows, with data for each scenario in rows. I also tried transposing to columns. I cant find a bar chart format that works for either table.

Thanks!

 

[Copernicus]

Yes, thats it! How do I get there!

 

[dave3009]

Well I used a helper range to sum the count of 1, 2 & 3's then plotted that.

Input data

Excel 2007

	A	B	C	D
1	Year	D1	D2	D3
2	2016	1	1	1
3	2017	1	1	1
4	2018	1	1	1
5	2019	1	1	1
6	2020	1	1	1
7	2021	1	1	1
8	2022	2	1	1
9	2023	2	2	1
10	2024	2	2	1
11	2025	3	2	1
12	2026	3	3	1
13	2027	3	3	1
14	2028	3	3	1
15	2029	3	3	1
16	2030	3	3	1
17	2031	3	3	1
18	2032	3	3	1
19	2033	3	3	1
20	2034	3	3	1
21	2035	3	3	1
22	2036	3	3	1
23	2037	3	3	1
24	2038	3	3	1

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Helper area

Excel 2007

	F	G	H	I
1		1	2	3
2	D1	6	3	14
3	D2	7	3	13
4	D3	23	0	0

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula G2 =COUNTIF($B$2:$B$24, G$1) H2 =COUNTIF($B$2:$B$24, H$1) I2 =COUNTIF($B$2:$B$24, I$1) G3 =COUNTIF($C$2:$C$24, G$1) H3 =COUNTIF($C$2:$C$24, H$1) I3 =COUNTIF($C$2:$C$24, I$1) G4 =COUNTIF($D$2:$D$24, G$1) H4 =COUNTIF($D$2:$D$24, H$1) I4 =COUNTIF($D$2:$D$24, I$1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Copernicus]

Thanks...other than labeling the horizontal axis with the year (without screwing everything up in the chart!) I got that to work.

 

[dave3009]

Cool, that's good. We're getting there

Last time I did a lot of chart work was a while back so quite rusty, I'll have a go at trying to get the axis labels to work. I can think of a quick cheat to do it, would that be satisfactory or are you needing to somehow manipulate this so it's dynamic?

 

[Copernicus]

No, the years will be fixed not dynamic. I could paste in an image of them but getting the years to match the data would be a pita.

 

[dave3009]

Like so?

 

[dave3009]

If you also plot 2016 as a value, then set the axis start point to 2016, max point to 2038 then set major gridlines to every 1 you get it to

Data

Excel 2007

	K	L	M	N
6	2016	6	3	14
7	2016	7	3	13
8	2016	23	0	0

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Outcome

 

[Copernicus]

exactly

 

[dave3009]

Have you managed to follow what I've been doing or do you need further instructions?

 

[Copernicus]

Have you managed to follow what I've been doing or do you need further instructions?

Ive gotten the bars, but not the years. Was there something I missed in the way of how you did it?