Can't figure this out...

[jmj713]

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?

 

[Sheeloo]

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.

 

[jmj713]

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
D316. 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]

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 D116 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

 

[jmj713]

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]

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))
,"")

 

[jmj713]

That changes #DIV/0! to FALSE. Can it make the cell blank? Or can I do that
with Conditional Formatting maybe? The reason I want to keep the formula is
that it's possible to add the missing number data later and so I want it to
update right away, without having to copy the formula.

 

[Sheeloo]

No, it is supposed to return a blank if count is zero
=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)),"")

You probably missed the "" at the end
type a
,""
just before the last ) in your formula which is giving you FALSE
or paste the formula above...

 

[Ron Rosenfeld]

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