[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
Medium Priority
192 Views
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
Question by:Tolgar
• 3
• 2

LVL 22

Accepted Solution

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

ID: 35459729

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

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

Thanks,
0

LVL 22

Expert Comment

ID: 35462144
In that case, would you please mark my answer as the solution.
0

Author Closing Comment

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

Thanks again,
0

Featured Post

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.
Suggested Courses
Course of the Month20 days, left to enroll