Solved

How to graph this data in Excel

Posted on 2011-03-18
9
247 Views
Last Modified: 2012-05-11

Help!  I don't know how to graph this data.
I have the attached a Sample of the data that I am trying to graph.  I am trying to create a bar graph.  
For each grade, there is an "Avg".  I would like it to show those tests below avg in red and those above on top in green for each grade level.  
I hope I am explaining myself well enough.  I have created an example of what I would like it to look like on the ss.  
Also, I would like to create another level besides the "Avg", that is "Less than 1/2 year behind" - If possible...

Thank you for any and all help!!!


Book1.xlsx
0
Comment
Question by:tacomarose
  • 3
  • 3
  • 3
9 Comments
 
LVL 24

Expert Comment

by:jimyX
Comment Utility
Check this out:
Book1-updated.xls
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
Comment Utility
Ahhrgh, JimyX, please ditch the 3D, please. Eye candy does not add any value whatsoever to a chart. Also, your chart just stacks the Median and the Average column. That's not what the asker is after.


tacomarose,

the way I read your question, you want a graph that shows the count of values below average and a count of values above average, stacked on top of each other.

See the attached file. I create two columns with formulas that use Countif() to count the values in each row that are below / above the "Avg" values. By the way, I don't know how you arrived at the value in the "Avg" column. If you use the =Average() function, to calculate the Average, it will differ significantly from the static values you put into that column. You may want to re-visit your "Average" calculation.

With regards to

>> Also, I would like to create another level besides the "Avg", that is "Less than 1/2 year behind" - If possible...

Your data does not have any identifiers for dates. It is not obvious which data is less than 1/2 years behind. Please explain how this can be identified from your data and start a new question for this separate issue.

cheers, teylyn

Book11.xlsx
0
 
LVL 24

Expert Comment

by:jimyX
Comment Utility
Hi Teylyn,
May be I misread this part of the question:
"I have the attached a Sample of the data that I am trying to graph.  I am trying to create a bar graph."

So I read that line and rushed to answer without paying full attention to the complete question. Well, I had to rush, because I know there are Excel Gurus, like you, who will jump on the question to answer (like last time ;-)
But surprisingly it was only my post that was there.

After all I was waiting for the author to respond and request the desired changes.

And regarding the 3D, I was not targeting the 3D Bar (although I like it) it just happened I clicked on it. Anyways it requires three mouse clicks, literally, to toggle between chart types.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
@jimyx
>> So I read that line and rushed to answer without paying full attention to the complete question.

There's the problem. :-)

>> Well, I had to rush, because I know there are Excel Gurus, like you, who will jump on the question to answer (like last time ;-)

I'm not a Guru.
I also don't "jump" on a question and post "something" to "get in" before the other experts. Instead, I try to understand what the asker wants to achieve. If I feel that I can contribute towards the solution, I will post.  If I don't feel that I can contribute towards a solution, I don't post.

Posting "some chart" and then "waiting for the author to respond and request the desired changes" is not my preferred approach. I try to understand the issue first. Then I try to find a solution and post that.

And, if you "like" 3D charts, I recommend that you do some research on best practice charting. It's not about "like", it's about readability. Start with Steven Few and Jon Peltier.

Cheers, teylyn
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 24

Expert Comment

by:jimyX
Comment Utility
>   "There's the problem"
What problem and whose problem? My contribution was related to the question, if it is not what the author wants it could be easily ignored rather than attacked.

>   "I'm not a Guru."
I know on EE, at this moment, you are Genius. I meant the English term. (if that what you mean)

>   "I also don't "jump" on a question and post "something" to "get in" before the other experts."
That's how an Expert with 2,000,000+ points act ;-)

>   " Instead, I try to understand what the asker wants to achieve. If I feel that I can contribute towards the solution, I will post.  If I don't feel that I can contribute towards a solution, I don't post."
Well, I felt I understood and I can contribute, then I did.

>   "do some research on best practice charting"
Will do, thanks for the advice.
0
 

Author Comment

by:tacomarose
Comment Utility
Wow!  Wasn't trying to cause drama. I apologize for not responding on Friday.  Had things that I had to leave and take care of.

JimyX - Thank you for trying to solve the issue.  Unfortunately, Teylyn's resolution is what I wanted.  

Teylyn - I'm assuming that I can do the same if I add another comparison.  Just do another countif and then just make it a different color?

Thanks!  
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Yes, you can add a new column of data to your table, then use the Select Data dialog to add the new data to the chart.

cheers, teylyn
0
 

Author Comment

by:tacomarose
Comment Utility
Hi Teylyn -
I'm a dork...  What is wrong w/ this formula that I don't see?
I added my other "standard" and I need to count the items greater that C38, but are less than C39.  It's giving me a count, but completely inaccurate.  
Sorry to bug you...
Thansk!

=COUNTIF(C3:C36,">="&C38)-COUNTIF(C3:C36,"<"&C39)
0
 

Author Comment

by:tacomarose
Comment Utility
Yikes!

I am most definitely a dork.  I hit submit and then figured out what I did wrong.  
Sorry again.  
And thank you for your help!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now