Can't figure this out...


J

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?
 
Ad

Advertisements

S

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.
 
J

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
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.
 
S

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

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!
 
S

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

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.
 
S

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...
 
Ad

Advertisements

R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top