Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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