Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to graph this data in Excel

Posted on 2011-03-18
9
Medium Priority
?
257 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 (Microsoft MVP / EE MVE) earned 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 50
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
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

916 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