Graphs of percentage by month

[Kijindoragon]

Hi, I'm creating a bar graph comparing month by month data (by percent) for 2015. Of course I only have data for January and February so far but I'm making this for other people and can't change it later. I've added the rest of the fields so they'll populate once data becomes available. However, the graph is now very wide with very narrow bars because there are essentially spacers where there will be data later. Is there a way that I can "hide" rows with no data automatically so they'll only take up space once there's something to show?

Thanks in advance!

 

[cyrilbrd]

Hi, yes there is a way. May I ask how is your raw data arranged?
Example:

Excel 2013
	A	B
1	month	data
2	jan	0.338228
3	feb	0.361635
4	mar
5	apr
6	april
7	may
Kijindoragon

 

[Kijindoragon]

Sure. The graph is set up with percentage on the y-axis and month and value on the x-axis.

each condition is an x-axis value which will ultimately have data for every month.

The data is set up for each month as follows:
Column A Column B
Condition 1 - percent of total
condition 2 - percent of total
condition 3 - percent of total
etc.

I can't just wait until there's data in the month to add it, as I need to give this to multiple people and I won't be able to edit it later.

Sorry I'm not sure how to upload a pic to make it easier

Thanks

 

[cyrilbrd]

mmmh so how many columns do you have?
you may post small sample of your data (not chart)
look at my signature for clues.

 

[Kijindoragon]

[TABLE="width: 276"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Variances[/TD]
[/TR]
[TR]
[TD]Late Organ Referral[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]50%[/TD]
[/TR]
[TR]
[TD]Late Tissue Referral[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0%[/TD]
[/TR]
[TR]
[TD]Missed Organ Referral[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4%[/TD]
[/TR]
[TR]
[TD]Missed Tissue Referral[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]21%[/TD]
[/TR]
[TR]
[TD]Pre-Approach[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]25%[/TD]
[/TR]
[TR]
[TD]Total Variances 2015[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So there's a list like that for each month and the graph will reflect that. Does that help?

 

[cyrilbrd]

maybe along this:

Excel 2013
	A	B	C	D
1	month	Cat	Values	%
2	1/1/2015	Late Organ Referral	14	50%
3	1/1/2015	Late Tissue Referral	0	0%
4	1/1/2015	Missed Organ Referral	1	4%
5	1/1/2015	Missed Tissue Referral	6	21%
6	1/1/2015	Pre-Approach	7	25%
7	2/1/2015	Late Organ Referral	18	72%
8	2/1/2015	Late Tissue Referral	1	4%
9	2/1/2015	Missed Organ Referral	1	4%
10	2/1/2015	Missed Tissue Referral	1	4%
11	2/1/2015	Pre-Approach	4	16%
12	3/1/2015	Late Organ Referral		#DIV/0!
13	3/1/2015	Late Tissue Referral		#DIV/0!
14	3/1/2015	Missed Organ Referral		#DIV/0!
15	3/1/2015	Missed Tissue Referral		#DIV/0!
16	3/1/2015	Pre-Approach		#DIV/0!
17	4/1/2015	Late Organ Referral		#DIV/0!
18	4/1/2015	Late Tissue Referral		#DIV/0!
19	4/1/2015	Missed Organ Referral		#DIV/0!
20	4/1/2015	Missed Tissue Referral		#DIV/0!
21	4/1/2015	Pre-Approach		#DIV/0!
Kijindoragon

Cell Formulas
Range	Formula
D2	=C2/SUMIF(A$2:A$21,A2,C$2:C$21)

than create Name range 'Data' referring to =OFFSET(Kijindoragon!$D$1,1,0,COUNTIF(Kijindoragon!$D:$D,">0"))

Now in your chart Series values is ='whatever_is_the_name_of_your_workbook.xlsm'!Data
And for the Horizontal (category) Axis labels simply use =Kijindoragon!$A$2:$B$21 or whatever range is used from January to December. (note change tab name as required)

The chart will display a column only for those month with data.

Would that work for you?

 

[Kijindoragon]

Will that work for this chart? The months are across the top because I'm charting the variables on the left together every month.
[TABLE="width: 1213"]
<colgroup><col><col><col><col><col><col><col span="3"><col><col><col><col><col></colgroup><tbody>[TR]
[TD]
Column1
[/TD]
[TD]Total[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[/TR]
[TR]
[TD]Late Organ Referral[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]44%[/TD]
[TD="align: right"]53%[/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD]Late Tissue Referral[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD]Missed Organ Referral[/TD]
[TD="align: right"]4%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]5%[/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD]Missed Tissue Referral[/TD]
[TD="align: right"]21%[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"]16%[/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0!
[/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD]Pre-Approach[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]22%[/TD]
[TD="align: right"]26%[/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"]#DIV/0![/TD]
[/TR]
</tbody>[/TABLE]

Sorry for no borders on the chart.

 

[cyrilbrd]

Given in A1:

Excel 2013
	A	B	C	D	E	F	G
1		total	1/1/2015	2/1/2015	3/1/2015	4/1/2015	5/1/2015
2	Late Organ Referral	50%	44%	53%	#DIV/0!	#DIV/0!	#DIV/0!
3	Late Tissue Referral	0%	0%	0%	#DIV/0!	#DIV/0!	#DIV/0!
4	Missed Organ Referral	4%	0%	5%	#DIV/0!	#DIV/0!	#DIV/0!
5	Missed Tissue Referral	21%	33%	16%	#DIV/0!	#DIV/0!	#DIV/0!
6	Pre-Approach	25%	22%	26%	#DIV/0!	#DIV/0!	#DIV/0!
Kijindoragon

Named ranges are:
LOR =OFFSET(Kijindoragon!$A$2,0,1,,COUNTIF(Kijindoragon!$2:$2,">=0"))
LTR =OFFSET(Kijindoragon!$A$3,0,1,,COUNTIF(Kijindoragon!$3:$3,">=0"))
MOR =OFFSET(Kijindoragon!$A$4,0,1,,COUNTIF(Kijindoragon!$4:$4,">=0"))
MTR =OFFSET(Kijindoragon!$A$5,0,1,,COUNTIF(Kijindoragon!$5:$5,">=0"))
PA =OFFSET(Kijindoragon!$A$6,0,1,,COUNTIF(Kijindoragon!$6:$6,">=0"))

Chart Legend Entries (Series)
Series Values 1 ='whatever_is_the_name_of_your_workbook.xlsm'!LOR
Series Values 2 ='whatever_is_the_name_of_your_workbook.xlsm'!LTR
Series Values 3 ='whatever_is_the_name_of_your_workbook.xlsm'!MOR
Series Values 4 ='whatever_is_the_name_of_your_workbook.xlsm'!MTR
Series Values 5 ='whatever_is_the_name_of_your_workbook.xlsm'!PA

Axis Label range is =Kijindoragon!$B$1:$N$1

Note:
Just a remark, keeping data in multiples Columns and few Rows may prove less efficient with bigger dataset...

 

[Kijindoragon]

Do I just copy the named ranges into all of the values? I'm sorry I'm so new to this... What I think you mean is

column b2 through b# is =offset....
column c2 through c# is =offset....

etc.

Are those abbreviations LOR LTR part of the field or are you just abbreviating that?

For the chart legend entries, do I need to specify the sheet as well as the workbook?