New-work-assignment - Ratings Sale Reps

Hello, I'm new here and I really need HELP. Im sorry for my english(not a native speaker) But I really hope you can help me...
I'm in a new job and boss wants a report from me, but I don't know how to do it. Im pretty decent with Excel, even really good with VBA but Pivot tables are hell for me.
I have a sheet of date with ratings of sales reps. Columns are Date,Sale_Rep_Name,Num_of_questions,Rating,Change,Rating_After_change. Every row is a rating of a Sales rep by customer, num of questions says how many questins did the cust. answer (max 4 questions). Rating is the rating given by cust. Change says if there was a manual change of rating by my boss (could be becouse the cust. didnt really understand the questionnaire) if there was something bad with the question(change = 1) then rating is changed to 1.
What I need to show on my report is connection between changes and number of questions. For example, would there be a less manual changes if we counted only ratings where cust. answered all four questions?
How to do this?

I understand I need table and pivot chart...but I just cant get it working...

What i want to know is, what would be the diference if we use only data where cust. answered all four questions (Num_of_questions = 4) or all data? Will there be more manual changes? (Change = 1) or not? Whole purpose of the report is to establish if the rating from customer that answered only one question is valid.

I'm drawing from my management experience and not anything to do with math or Excel or formulas.

If you perform a customer survey on anything and you utilize 100 questions that every customer answers completely ... then if you use a survey
of 6 questions that every customer answers completely .... the relative result of both will be accurate within an acceptable norm.

This is of course dependent upon the questions asked (are they all on topic compared to one another?) and have you asked more than just
a few.

Definition of "few" ? Say 1/2 dozen or so ?

Example: managed a medical department at a large regional hospital in the S.E. US. Sometimes we asked a survey of 25 questions and sometimes
only 5 or 6 questions - all on the same topic. Just worded slightly different. The results from both surveys were reasonably the same. Any difference
was not worth mentioning or considering.

So, the quality of the survey result has more to do with the quality of the questions, than how many.

I am confident if you search the Net for information regarding this comparison you will find it to be true.

But is there a way in excel to tell me in %, how many less or more changes there are if I use data were cust. answered all 4 questions?

I'm with FDibbins .. not certain what you are referring to .

Your english is fine. Don't worry.

As I understand your situation .. the question "Were you satisfied? 1-yes /2-no " is accurately appropriate. I would be inclined to say that using only that single question is a viable survey ... whether or not the customer
answers the remaining questions.

However ...

I believe your concern may actually be deeper than just the one question. Is that single question fair to the sales rep ... whether or not they keep their money or have it taken from them.
My opinion (from both the employer's view and the saleman's view) basing your decision on this one question is not fair, especially because of the final affect it will have on the saleman.

For example: no matter what your product is - intellectual, something physical, entertainment, etc. it all involves some level of selling. Today's customer is more knowledgeable, demanding and aggressive. A percentage of
customers cannot be pleased no matter what you do - even giving them the product for free ! (Personal experience with several customers) So. in this circumstance, basing your decision if the customer was pleased is making it unfair for the salesman.

I personally, even as an employer, would want to know more from the customer before making a financial decision for my employee ... unless your company simply intends to not provide a fair wage - which I doubt.

Yes!Thats my point. But I need numbers to show it to my boss. So again, is there a way to use pivot table to tell me: If you use only data with 4 questions you will be doing less manual changes. But how?

We need an Excel Formula Guru for this ! Of which, I am not. Sorry.

Any Formula Guru ??? Please ..

The formula will provide a mathematical explanation of what you seek, but I believe you need more than that to convince your employer. Here are some suggestions.

Two resources :

https://hbr.org/2002/02/getting-the-...kplace-surveys

http://www.docurated.com/all-things-...ncy-sales-team


See attachments for suggestions:

Hope this helps in suggesting a different format. With a new presentation and some help from a FORMULA GURU ... you should be good to go !