|
Can't figure this out...
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? |
Can't figure this out...
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? |
Can't figure this out...
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? |
Can't figure this out...
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? |
Can't figure this out...
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? |
Can't figure this out...
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? |
Can't figure this out...
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" wrote: 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? |
Can't figure this out...
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... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "jmj713" wrote: 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" wrote: 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? |
Can't figure this out...
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 |
| All times are GMT +1. The time now is 03:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com