So frustrating, I'm hoping someone can help me, because I know Excel can do
anything. This is a bit complex, so I'll try to explain it the best I can:

I need to be able to get a percentage of occurrences of YES and NO values as
they pertain to a table with numerical values. To illustrate, I may have
something like this:

1 YES
1 YES
2 NO
4 YES
4 NO

etc.

In this example, "1" gets YES 100% of the time, "2" gets YES 0% of the time,
and "4" gets YES 50% of the time. Is this doable?

Put the distinct values in Col A have in Celss C1, C2, C3,... (1,2,4 in your
example)
Then enter this formula in D1
=SUMPRODUCT(--($A$1:$A$30=D1),--($B$1:$B$30="Yes")/COUNTIF($A$1:$A$30,D1))
Adjust 30 to the end of your data and copy down. Format the cells as
Percentage...

You can change Yes to No to get percentage of Nays.

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"jmj713" wrote:

So frustrating, I'm hoping someone can help me, because I know Excel can do
anything. This is a bit complex, so I'll try to explain it the best I can:

I need to be able to get a percentage of occurrences of YES and NO values as
they pertain to a table with numerical values. To illustrate, I may have
something like this:

1 YES
1 YES
2 NO
4 YES
4 NO

etc.

In this example, "1" gets YES 100% of the time, "2" gets YES 0% of the time,
and "4" gets YES 50% of the time. Is this doable?

Hm, I'm adjusting your formula to my data but getting a circular reference
warning. My values (1, 2, etc.) are in C3:C16 and the YES and NO are in
D3:D16. Then separately, in column M, I have a table of 1, 2, 3, etc. and
need to have percentages like I explained below for each. Can you explain a
little more how to implement this formula? Thank you for the speedy reply.

"Sheeloo" wrote:

Put the distinct values in Col A have in Celss C1, C2, C3,... (1,2,4 in your
example)
Then enter this formula in D1
=SUMPRODUCT(--($A$1:$A$30=D1),--($B$1:$B$30="Yes")/COUNTIF($A$1:$A$30,D1))
Adjust 30 to the end of your data and copy down. Format the cells as
Percentage...

You can change Yes to No to get percentage of Nays.

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"jmj713" wrote:

So frustrating, I'm hoping someone can help me, because I know Excel can do
anything. This is a bit complex, so I'll try to explain it the best I can:

I need to be able to get a percentage of occurrences of YES and NO values as
they pertain to a table with numerical values. To illustrate, I may have
something like this:

1 YES
1 YES
2 NO
4 YES
4 NO

etc.

In this example, "1" gets YES 100% of the time, "2" gets YES 0% of the time,
and "4" gets YES 50% of the time. Is this doable?

Try
=SUMPRODUCT(--($C$1:$C$16=M1),--($D$1:$D$16="Yes")/COUNTIF($C$1:$C$16,M1))
Explanation:
SUMPRODUCT(--($C$1:$C$16=M1),--($D$1:$D$16="Yes") looks for M1 in C1:C16 AND
Yes in D1:D16 and counts if both are found...
COUNTIF counts C1:C16 where value matches M1

You can either format as percentages or multiply the formula by 100
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"jmj713" wrote:

Hm, I'm adjusting your formula to my data but getting a circular reference
warning. My values (1, 2, etc.) are in C3:C16 and the YES and NO are in
D3:D16. Then separately, in column M, I have a table of 1, 2, 3, etc. and
need to have percentages like I explained below for each. Can you explain a
little more how to implement this formula? Thank you for the speedy reply.

"Sheeloo" wrote:

Put the distinct values in Col A have in Celss C1, C2, C3,... (1,2,4 in your
example)
Then enter this formula in D1
=SUMPRODUCT(--($A$1:$A$30=D1),--($B$1:$B$30="Yes")/COUNTIF($A$1:$A$30,D1))
Adjust 30 to the end of your data and copy down. Format the cells as
Percentage...

You can change Yes to No to get percentage of Nays.

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"jmj713" wrote:

So frustrating, I'm hoping someone can help me, because I know Excel can do
anything. This is a bit complex, so I'll try to explain it the best I can:

I need to be able to get a percentage of occurrences of YES and NO values as
they pertain to a table with numerical values. To illustrate, I may have
something like this:

1 YES
1 YES
2 NO
4 YES
4 NO

etc.

In this example, "1" gets YES 100% of the time, "2" gets YES 0% of the time,
and "4" gets YES 50% of the time. Is this doable?

Excellent, it works just like it should, thanks so much!

One additional question: is it possible to modify the formula so that if it
does not find a number, like 5 for example, it ignores that? Right now I
copied the formula down and some numbers are not represented in the table at
all, so I'm getting a division by zero error. I guess I could just clear
those cells by hand, but is it possible to do something automatically? If
not, it's not a big deal, this is a huge help as it is!

"Sheeloo" wrote:

Try
=SUMPRODUCT(--($C$1:$C$16=M1),--($D$1:$D$16="Yes")/COUNTIF($C$1:$C$16,M1))
Explanation:
SUMPRODUCT(--($C$1:$C$16=M1),--($D$1:$D$16="Yes") looks for M1 in C1:C16 AND
Yes in D1:D16 and counts if both are found...
COUNTIF counts C1:C16 where value matches M1

You can either format as percentages or multiply the formula by 100
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"jmj713" wrote:

Hm, I'm adjusting your formula to my data but getting a circular reference
warning. My values (1, 2, etc.) are in C3:C16 and the YES and NO are in
D3:D16. Then separately, in column M, I have a table of 1, 2, 3, etc. and
need to have percentages like I explained below for each. Can you explain a
little more how to implement this formula? Thank you for the speedy reply.

"Sheeloo" wrote:

Put the distinct values in Col A have in Celss C1, C2, C3,... (1,2,4 in your
example)
Then enter this formula in D1
=SUMPRODUCT(--($A$1:$A$30=D1),--($B$1:$B$30="Yes")/COUNTIF($A$1:$A$30,D1))
Adjust 30 to the end of your data and copy down. Format the cells as
Percentage...

You can change Yes to No to get percentage of Nays.

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"jmj713" wrote:

So frustrating, I'm hoping someone can help me, because I know Excel can do
anything. This is a bit complex, so I'll try to explain it the best I can:

I need to be able to get a percentage of occurrences of YES and NO values as
they pertain to a table with numerical values. To illustrate, I may have
something like this:

1 YES
1 YES
2 NO
4 YES
4 NO

etc.

In this example, "1" gets YES 100% of the time, "2" gets YES 0% of the time,
and "4" gets YES 50% of the time. Is this doable?

Try
=IF(COUNTIF($C$1:$C$16,M1)
0,SUMPRODUCT(--($C$1:$C$16=M1),--($D$1:$D$16="Yes")/COUNTIF($C$1:$C$16,M1))
,"")
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"jmj713" wrote:

Excellent, it works just like it should, thanks so much!

One additional question: is it possible to modify the formula so that if it
does not find a number, like 5 for example, it ignores that? Right now I
copied the formula down and some numbers are not represented in the table at
all, so I'm getting a division by zero error. I guess I could just clear
those cells by hand, but is it possible to do something automatically? If
not, it's not a big deal, this is a huge help as it is!

"Sheeloo" wrote:

Try
=SUMPRODUCT(--($C$1:$C$16=M1),--($D$1:$D$16="Yes")/COUNTIF($C$1:$C$16,M1))
Explanation:
SUMPRODUCT(--($C$1:$C$16=M1),--($D$1:$D$16="Yes") looks for M1 in C1:C16 AND
Yes in D1:D16 and counts if both are found...
COUNTIF counts C1:C16 where value matches M1

You can either format as percentages or multiply the formula by 100
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"jmj713" wrote:

Hm, I'm adjusting your formula to my data but getting a circular reference
warning. My values (1, 2, etc.) are in C3:C16 and the YES and NO are in
D3:D16. Then separately, in column M, I have a table of 1, 2, 3, etc. and
need to have percentages like I explained below for each. Can you explain a
little more how to implement this formula? Thank you for the speedy reply.

"Sheeloo" wrote:

Put the distinct values in Col A have in Celss C1, C2, C3,... (1,2,4 in your
example)
Then enter this formula in D1
=SUMPRODUCT(--($A$1:$A$30=D1),--($B$1:$B$30="Yes")/COUNTIF($A$1:$A$30,D1))
Adjust 30 to the end of your data and copy down. Format the cells as
Percentage...

You can change Yes to No to get percentage of Nays.

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"jmj713" wrote:

So frustrating, I'm hoping someone can help me, because I know Excel can do
anything. This is a bit complex, so I'll try to explain it the best I can:

I need to be able to get a percentage of occurrences of YES and NO values as
they pertain to a table with numerical values. To illustrate, I may have
something like this:

1 YES
1 YES
2 NO
4 YES
4 NO

etc.

In this example, "1" gets YES 100% of the time, "2" gets YES 0% of the time,
and "4" gets YES 50% of the time. Is this doable?

On Fri, 10 Apr 2009 09:02:05 -0700, jmj713
wrote:

So frustrating, I'm hoping someone can help me, because I know Excel can do
anything. This is a bit complex, so I'll try to explain it the best I can:

I need to be able to get a percentage of occurrences of YES and NO values as
they pertain to a table with numerical values. To illustrate, I may have
something like this:

1 YES
1 YES
2 NO
4 YES
4 NO

etc.

In this example, "1" gets YES 100% of the time, "2" gets YES 0% of the time,
and "4" gets YES 50% of the time. Is this doable?

Try a Pivot Table.

These directions are for Excel 2007, but I believe will also work in earlier
versions, although the options may not be in the same place.

1. Label each of the columns. I chose "Values" and "YN" as the labels.

Insert/Pivot Table.

Drag
Values to Rows
YN to Columns
YN to Values area (or Data area)

Right click in the data area and choose Summarize Data By : More Options
Choose to Summarize by Count
Show values as Percent of Row

Format other options to taste.
--ron