Excel 2003 Pivot Chart displaying added values?

[myexcel4me]

Hello,

I'm using Windows XP with MS Excel 2003. I have a pivot table representing a survey. Let's say I've built the survey outside of excel and I've imported the response data into Excel. One of the questions in the survey is "ratings" and the possible valid responses for it is: "Excellent", "Good", or "Poor". In my data set in excel let's say I have 10 responses or rows and all the responses for the question on ratings are either "Excellent" or "Good". (There are no rows with a "Poor" value in the ratings column).
For example, let's say out of the 10 responses, 6 are "Excellent" and 4 are "Good". As such my Pivot chart shows two bars: one for the number of respones with "Excellent" (10) and another bar for the number of respones with "Good" (4). My delima is how to show a third bar showing "Poor" with a zero as the number of responses.

Any ideas? Much appreciated.

 

[curtishavak]

I assume you are organizing your pivot table with the responses and questions in the column and row sections, respectively, or vice versa. The data field would then be a count_of the responses. I also assume your data set is set up in columns something like:

A / B / C / D
Respondent Name or Number / Misc. Identifying Info / Question Number / Response

The response column contains one of the three values (Excellent, Good, Poor).

Correct?

If this is the case, the pivot table must have a value in the data set which includes a response of "Poor" in order to be able to display it. So, I don't think with your data formatted this way that you will get what you're looking for without adding a control entry containing values of poor, which would skew your data since then you would have at least one value of "Poor" in your Pivot where none existed in the actual survey responses.

Instead, I would split my response into independent columns with either a 1 or 0 to indicate a response or not. Base data would look something like:

A / B / C / D / E / F
1) Respondent Name or Number / Misc. Identifying Info / Question Number / "Excellent" Response / "Good" Response / "Poor" Response
2) Resp #1 / XXX / Question A / 1 / 0 / 0
3) Resp #1 / XXX / Question B / 0 / 1 / 0
4) Resp #2 / XXX / Question A / 0 / 1 / 0
5) Resp #2 / XXX / Question B / 1 / 0 / 0

This way, you could drop "Question Number" field into the row field in your pivot and then each of the "Excellent" Response / "Good" Response / "Poor" Response fields into your data. This would give you a pivot that looked something like:

A / B / C / D / E / F
1) / "Excellent" Response / "Good" Response / "Poor" Response
2) Resp. Number
3) Resp #1 / 1 / 1 / 0
4) Resp #2 / 1 / 1 / 0


You could also add question number into either a row or column set-up to further parse out your response data.

As for making the change to your base data, you could just set up a simple =IF formula:

=if(A1="Excellent",1,0) in your excellent column;
=if(A1="Good",1,0) in your good column;
=if(A1="Poor",1,0) in your poor column

Further, if you wanted to, you could weight your answers to get average values: Excellent = 3; Good = 2, Poor = 1. Then you could use Average Of instead of Count Of to give an overall scored value for each question and/or respondent to rate your overall survey performance. The would just change your =IF formulas to:

=if(A1="Excellent",3,0) in your excellent column;
=if(A1="Good",2,0) in your good column;
=if(A1="Poor",1,0) in your poor column.


Hope this helps.

 

[myexcel4me]

Thank you very much, curtishavak, for your quick response

Your assumption about my current data set is correct. You feedback is very helpful since it showed the flaw in the current format of my data set. You are right, there would be no way of showing the "Poor" value, but instead your suggestion of reformatting the data set to include a 1 or 0 to indicate a response makes sense and will allow me to display the data the way I want!!
Also, your suggested formula for parsing and reformatting the data is easy to do. Again thank you very much. You have my applause

Kudos to this website, MrExcel.com, for allowing me to post my question and it's fabulous to get an excellent response.

Take care