Trying to translate data into a pie chart with no success!

AnD33B · Jun 14, 2015

Hi guys, I was wondering if someone could steer me in the right direction? I was handed the below table at work and the boss basically wants me to calculate the sum of each response using a formula and then translate that data into a pie chart. Now at first I thought yeah this is basic, so I all I did was a formula like this =COUNTIF(B4:B13,"*") making the asterisk represent a value of 1, then tallied up the total of every column, so obviously Strongly Agree would end up been 5 and the rest would follow suit. That's no problem but when I tried converting this data to a pie chart I'd get something like the image supplied , which isn't quite right.
Is it because of the chart not been able to recognize the asterisk as a value or it because I used a bad formula and set it out wrong because all I did was add another line of cells underneath employee 10 as Total and calculated all the sums from Strongly Agree to Strongly Disagree?
I hope what I said makes sense and someone can help fix this mess? Obliged if anyone can help me sort this out

[TABLE="class: MsoNormalTable, width: 379"]
<tbody>[TR]
[TD="width: 506, colspan: 5"]

I found the information provided in the guide to be very useful

[/TD]
[/TR]
[TR]
[TD="width: 133"][/TD]
[TD="width: 112"]

Strongly Agree

[/TD]
[TD="width: 54"]

Agree

[/TD]
[TD="width: 73"]

Disagree

[/TD]
[TD="width: 133"]

Strongly Disagree

[/TD]
[/TR]
[TR]
[TD="width: 133"] Employee 1[/TD]
[TD="width: 112"]

*

[/TD]
[TD="width: 54"][/TD]
[TD="width: 73"][/TD]
[TD="width: 133"][/TD]
[/TR]
[TR]
[TD="width: 133"] Employee 2[/TD]
[TD="width: 112"]

*

[/TD]
[TD="width: 54"][/TD]
[TD="width: 73"][/TD]
[TD="width: 133"][/TD]
[/TR]
[TR]
[TD="width: 133"] Employee 3[/TD]
[TD="width: 112"][/TD]
[TD="width: 54"]

*

[/TD]
[TD="width: 73"][/TD]
[TD="width: 133"][/TD]
[/TR]
[TR]
[TD="width: 133"] Employee 4[/TD]
[TD="width: 112"][/TD]
[TD="width: 54"][/TD]
[TD="width: 73"]

*

[/TD]
[TD="width: 133"][/TD]
[/TR]
[TR]
[TD="width: 133"] Employee 5[/TD]
[TD="width: 112"][/TD]
[TD="width: 54"]

*

[/TD]
[TD="width: 73"][/TD]
[TD="width: 133"][/TD]
[/TR]
[TR]
[TD="width: 133"] Employee 6[/TD]
[TD="width: 112"]

*

[/TD]
[TD="width: 54"][/TD]
[TD="width: 73"][/TD]
[TD="width: 133"][/TD]
[/TR]
[TR]
[TD="width: 133"] Employee 7[/TD]
[TD="width: 112"]

*

[/TD]
[TD="width: 54"][/TD]
[TD="width: 73"][/TD]
[TD="width: 133"][/TD]
[/TR]
[TR]
[TD="width: 133"] Employee 8[/TD]
[TD="width: 112"][/TD]
[TD="width: 54"]

*

[/TD]
[TD="width: 73"][/TD]
[TD="width: 133"][/TD]
[/TR]
[TR]
[TD="width: 133"] Employee 9[/TD]
[TD="width: 112"][/TD]
[TD="width: 54"]

*

[/TD]
[TD="width: 73"][/TD]
[TD="width: 133"][/TD]
[/TR]
[TR]
[TD="width: 133"] Employee 10[/TD]
[TD="width: 112"]

*

[/TD]
[TD="width: 54"][/TD]
[TD="width: 73"][/TD]
[TD="width: 133"]

[/TD]
[/TR]
</tbody>[/TABLE]

RudiS · Jun 14, 2015

Have you tried Switching Rows/Columns on the chart ribbon?
I think your chart is simply plotting columns where you need it to ploy by row!

Alternatively,

Select the range B3:E3
Hold CTRL and select B14:E14
Then click the Pie Chart

AnD33B · Jun 15, 2015

Hey thanks for replying RudiS, I forgot to mention I did try many other things like you suggested but for some reason the pie charts just don't look the way they "should" I think it's because there are totals that simply amount to 0 and the pie chart simply ignores them, if that makes sense? I think another graph like a column or line would have made more sense but who am I to question authority, LOL?
Anyway I tried switching the Rows/Columns and this is what I got, LOL.

As you can see, not very helpful is it? However if I delete all the employee fields on the left hand side under Legend Entries and only leave the "Total" field remain I get something that resembles what I think it should look like:

Naturally it only shows the fields that had tallies, everything with a 0 value doesn't show up, I get the same result if I try your alternative method, doesn't look very impressive, even if it's correct imo.
However if I replace the asterisks with a "1" as a value and replace the formula with a basic =SUM, the pie chart looks a little more impressive:

But at the end of the day I'm not 100% sure if that's what he wants, I mean that one "1" value doesn't really make sense in terms of the original survey data and looks slightly out of place imo and like I stated previously, who am I to question authority?, so I guess what I'm asking is do you think I should stick with my original formula and try your alternative method or use the new method, as tbph I'm not sure the second method looks like it makes sense? Bleh sorry for the elaborate post it's just really infuriating when something should be so simple!

RudiS · Jun 15, 2015

A pie chart can only plot a single range of values (with a range of labels). IOW, line, bar and column charts can ploy a block of data where each column or row represents a separate series on the chart. A pie chart only has one series, so you cannot select a block of cells that contain multiple rows and columns.

Would it be possible to upload a desensitized sample workbook? or see if this sample file provides further assistance....

AnD33B · Jun 16, 2015

Thanks very much for your input RudiS I think I will just go with something along the lines of your sample file and see whether or not that is what they're after, if not, I'm sure they will tell me, I will probably suggest a different style graph if that's the case, much appreciated!

Trying to translate data into a pie chart with no success!

AnD33B

New Member

Excel Facts

RudiS

Active Member

AnD33B

New Member

RudiS

Active Member

AnD33B

New Member

Similar threads

Forum statistics

Share this page

Trying to translate data into a pie chart with no success!

AnD33B

New Member

Excel Facts

RudiS

Active Member

AnD33B

New Member

RudiS

Active Member

AnD33B

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock