Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to graph this data in Excel

Posted on 2011-03-18
9
250 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
ID: 35168266
Check this out:
Book1-updated.xls
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 35171252
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
ID: 35171418
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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35171601
@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
 
LVL 24

Expert Comment

by:jimyX
ID: 35171864
>   "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
ID: 35181116
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:Ingeborg Hawighorst
ID: 35183961
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
ID: 35194232
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
ID: 35194235
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Return only first numeric digits in a seperate column 11 54
combine fist two words in a cell 2 23
MS Excel Multi Sheet Formula 13 31
Clear a Text Box 7 23
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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

809 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