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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok It worked. I don't know what I have changed actually.
Thanks,
Thanks,
<<Thank you for your reply. It is exactly what I wanted.>>
In that case, would you please mark my answer as the solution.
In that case, would you please mark my answer as the solution.
ASKER
Sorry, I was so busy during the day. Just have chance to close this question.
Thanks again,
Thanks again,
ASKER
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
Why do you think so?
Thanks,