• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to create one chart with two different series of variables, both in columns

A

Arachne53

New Member
I need to create a chart in Excel 2010 that shows the average number of credits students have attempted, the avg number attained, and the success rate as a %. The first two items are just plain numbers usually in the 5.1 to 10.2 range. However, the success rate is a % more like 84%. Obviously, the same scale won't work for both. I know you can create a secondary axis but, when I do that, the secondary axis columns overlay the primary columns and nothing I can do changes that.


I know I can create a combo chart but all the examples I find are combination of column and line which isn't appropriate for my chart.


Thanks for any help that you can offer,

Kate
 
Hi Arachne53,


Why not use line charts throughout?? They will not overlay to hide data of primary axis?


Faseeh
 
I don't think she is trying to display over time, so just bar graphs are necessary.


Go to format data series, then change the overlay properties.

Upload load a sample file so I can try and do it for you.
 
Faseeh, thank you for your reply but, Montrey is correct - a line really won't show what I need to show since it is a point-in-time, not a progression.


Montrey, I have a sample file but don't know how to post it here. I'm positive I saw something about posting files but I can't seem to find it again. :-(


Kate
 
Kate,


Check out the sticky at the top of the forum:

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
One idea with the column charts is to use some dummy series to get things aligned correctly.

Example

[pre]
Code: 
Stuff_on_primary_axis

Avg# attempted    Avg# Attained     Dummy#1
5                    4            0
3                    3.5          0
5                    4.5          0

Stuff_on_secondary_axis

Dummy#2      Dummy#3    Success Rate
0              0            88%
0              0            91%
0              0            95%
[/pre]
By putting 3 series on each axis, the alignment works out so that the 3 series we care about aren't on top of each other. If you're using a legend, you can single-click twice on a legend entry and then hit 'delete' to remove the Dummy series labels.
 
Thanks, Luke, for both the sticky note link and the suggestion. I will check out your suggestion using dummy data but, in the meantime, here is link to the sample chart I uploaded. It is MS Word 2010: http://speedy.sh/djTK9/For-Chandoo.doc


Thanks again,

Kate
 
Luke, I tried working with your dummy idea and had my resident Excel guru take a crack at it, too, but we just couldn't make it behave. When you have a minute, would you work up a dummy file and post it so that I can see how it works, please? Since I work in the research and analytics department of a college, I'm guessing it would be very, very handy to be able to show this type of comparison since we're all about success. :)


Thanks,

Kate
 
Kate!


Check this out. Took me a while to figure out a decent way to display the data. I think you will be pleased!


http://speedy.sh/WVG5m/Graph-for-Kate.xls


The graph kinda has a "glass half full effect" I hope you like it!
 
I'm afraid I can't access uploaded workbooks at my location, but I'll attempt a step-by-step instruction. Starting with this data in A1:G4

[pre]
Code: 
<blank>  attempted	attained     dummy	Dummy#2	Dummy#3	Success
Name a	    5	           4	       0	  1	  1	  88%
Name b	    3	          3.5	       0	  1	  1	  91%
Name c	    5	          4.5	       0	  1	  1	  95%
[/pre]
Select cells A1:G4. Insert, Chart. Select column Chart. Flip rows/columns if needed so that Names appear as the y-axis labels. Chart should have 6 columns in it.


Select Success series, format series, move to secondary axis.

Repeat for Dummy#2 and Dummy#3.

Change values for Dummy#2 and Dummy#3 series to 0 (I only had them at 1 so we could see on chart).

Format secondary axis to percentage.


If legend is being used, single click, twice, on a dummy series label and hit the delete key. Repeat as needed.


Hope that works.
 
Montrey,

Correct, only 3. I was just using an overlay method. Primary axis uses first 2 slots and an empty third, secondary axis uses empty 1st and 2nd, active 3rd.
 
Yea mine kinda does the same thing. Need that fake data in there. Sorry I didnt understand what you were tryin to do brotha!


Good work
 
You must log in or register to reply here.
Back
Top