Link to home
Start Free TrialLog in
Avatar of Tolgar
Tolgar

asked on

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

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
ASKER CERTIFIED SOLUTION
Avatar of Nico Bontenbal
Nico Bontenbal
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Tolgar
Tolgar

ASKER

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,
Avatar of Tolgar

ASKER

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

Thanks,
<<Thank you for your reply. It is exactly what I wanted.>>
In that case, would you please mark my answer as the solution.
Avatar of Tolgar

ASKER

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

Thanks again,