[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to do analysis for a set of data in Excel with graphs?

Posted on 2011-04-22
5
Medium Priority
?
192 Views
Last Modified: 2012-05-11
Hi,
I have a set of data and I wan to do some analysis on this data. You can find my example excel sheet (book1.xlsx) in the attachment.

The graphs that I want to draw are:

1- Which design tools each team uses. X axis will be Design tools. Y axis is how many people responded what tool. It will be a stack bar graph that each bar will represent one design tool. In each bar we will see each area by color. So there will be 6 bars (Visio, Magicdraw, Word, Gimp, Powerpoint, Umbrello) and in each bar there will be 3 colors for each area (Area 1, Area 2, Area 3)

Note: for example person2-3-4 selected multiple design tools. In this case I want to see each tool separately, because word and gimp are repeated tools.

2- In the last 3 columns, 1 means rarely, 5 means frequently. Again I want to see how frequently they are used on a weighted graph by area on a stack bar graph. Visually we can say that Area1 uses Ruby at a rate of 1, Area2 at a rate of 3, Area3 at a rate of 5+2 and so on for PHP and .NET


Note: Each bar will represent Ruby, PHP and .NET . So there will be 3 bars. In each bar, we will see each area by color. so there will be 3 colors on each bar.

This data is just an example. I have a huge set of data. How can I make this using Excel 2007. I  tried using pivot table but I couldn't manage to do it.

Can you please send me a working example for these two cases?

Thanks,

Book1.xlsx
0
Comment
Question by:Tolgar
  • 3
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 35455319
Something like this?
Book1.xlsx
In G2:L5 I used the search function to check if column C contains a certain text. Then on B9:G11 I used the SumIf you create a total for each tool for each area. Then the graph is easy.
The same technique is used for the Platform frequency. The range D2:F5 contained text and not numbers in the example. I converted those to numbers.
0
 

Author Comment

by:Tolgar
ID: 35459729
Thank you for your reply. It is exactly what I wanted.

However, when I type the following command in a cell it does not evaluate the value. I just see the function itself.

=IF(ISERROR(SEARCH(F$1,$E2)),0,1)

Why do you think so?


Thanks,
0
 

Author Comment

by:Tolgar
ID: 35459818
ok It worked. I don't know what I have changed actually.

Thanks,
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35462144
<<Thank you for your reply. It is exactly what I wanted.>>
In that case, would you please mark my answer as the solution.
0
 

Author Closing Comment

by:Tolgar
ID: 35464503
Sorry, I was so busy during the day. Just have chance to close this question.

Thanks again,
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

873 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