• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

How to graph this data in Excel


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
tacomarose
Asked:
tacomarose
  • 3
  • 3
  • 3
1 Solution
 
jimyXCommented:
Check this out:
Book1-updated.xls
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
jimyXCommented:
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@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
 
jimyXCommented:
>   "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
 
tacomaroseAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
tacomaroseAuthor Commented:
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
 
tacomaroseAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now