Pivot table showing percentage of positive answer according to quintile

Hello,

This is a summary of how I have the data organized:
- Company name
- Company quintile according to revenue
- Answers 1 to n: answers given by each company to certain questions

Company Revenue quintil Answer1 Answer2 ...
A I Yes Yes
B II Yes No
C III Yes Yes
D IV No Yes
E V No Yes
F I No No
G II Yes No
H III Yes Yes
I IV Yes Yes
J V Yes No

I'd like to have a summary table showing per quintile, the % of all companies answering "Yes" to each question, it is:

I II III IV V
Answer1 50% 100% 100% 50% 50%
Answer2 50% 50% 100% 100% 50%

If this is not posible, it would be also useful to have information under each answer showing the number of answers "Yes" and "No":

I II III IV V
Answer1
Yes 1 2 2 1 1
No 1 0 0 1 1
Answer2
Yes 1 1 2 2 1
No 1 1 0 0 1

I'd appreciate any help you can provide.

Thanks a lot!

Hello Richard,

Thanks a lot for your help. Is it possible to do this using Pivot Tables?

I am looking for table like this:
____________I II III IV V
Answer1
____Yes _____1 2 2 1 1
____No _____1 0 0 1 1
Answer2
____Yes _____1 1 2 2 1
____ No _____1 1 0 0 1

Thans again!

Arcturus

The following file uses a modification to Richard's table to give the format that you are asking about. It uses the formulas:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Hi everyone.

I hope this attached file could help.

I came back to this with a single formula (which can be pasted into cell C14 and then copied down and across):

Please Login or Register  to view this content.

Copy of QuintileQ.xlsx

Let me know if you have any questions.