Problem in data analysis

[Shweta Srivastava]

Owner P-A P-B P-C P-D P-E
A Yes Yes Yes Yes Yes
B Yes Yes Yes Yes Yes
C Yes Yes Yes Yes Yes
D Yes Yes Yes Yes Yes
A Yes Yes Yes Yes Yes
B Yes Yes Yes Yes Yes
C No No No No No
D No No No No No
A No No No No No
B No No No No No
C No No No No No
D No No No No No
A No No No No No
B No No No No No
C No No No No No
D No No No No No

1. For all Owner (column 1st)- what is the count of YES or NO for all the
parameters from column P-A to P-E?
2. Which parameter has maximum "NO" or "Yes" in total ?

I tried using Pivot but still not able to make it. How can it be done??

Would appreciate if somebody can help me at the earliest

 

[Shane Devenshire]

I'm not clear on the question:

1. For all Owner (column 1st)- what is the count of YES or NO for all the
parameters from column P-A to P-E?
2. Which parameter has maximum "NO" or "Yes" in total ?

do you mean for each Owner watn t0 count of Yes and No answers?
do you mean that you want to know which column has the most yes and no
answers?

Suppose you enter
A
B
C
D
in H1:H4
Then in I1 use

=SUMPRODUCT(--(A$2:A$17=H1)*(B$2:F$17="Yes"))

In J1 use

=SUMPRODUCT(--(A$2:A$17=H1)*(B$2:F$17="No"))

copy these down to I4:J4.

Now your second question is a problem because you haven't told us how to
treat ties and your same data is all tied. Every column has 6 yes and 10
no's.


If so

 

[Shane Devenshire]

Sorry, forgot, I wanted to make a comment regarding pivot tables. Your data
is not really set up for a pivot table it should look like this:

Owner Parameter Result
A P-A Yes
B P-B No
....

 

[Shweta Srivastava]

Hi Shane,
Let me rephrase my query once again so as to give you proper understanding.

Say I have A,B,C,D engineers under the column Owner.
and 5 parameters (P-A to P-E) to test if every engineer has followed the
parameter as defined in the form of "Yes" or "NO".

Now I want to analyse:
1) How many "Yes" or "NO" each Engineer/owner-A, B, C & D has got under each
parameter.
2) Which Parameter is having maximum "NO"..so that one can judge that this
particular parameter needs to be focused more

I hope am able to describe the query more appropriately, this time.

Shweta

2)