stevestojan Posted November 22, 2005 Share Posted November 22, 2005 Ok, I'm making a very basic spreadsheet, but I can't figure out the one formula. Basically, I want one column to be a yes/no in each cell. So, for example, Column C would be "Sale Made?" and each cell would have either Y or N in it. I then want the last cell in that column to show the percentage of Ys. So, If C1 was Y, C2 was Y, C3 was N and, C4 was Y, C5 would read "75" or "75%" It seems simple enough, but I can't think of what the hell to do.... any help would be awesome. Thanks Link to comment Share on other sites More sharing options...
meazza Posted November 22, 2005 Share Posted November 22, 2005 Ok, I'm making a very basic spreadsheet, but I can't figure out the one formula. Basically, I want one column to be a yes/no in each cell. So, for example, Column C would be "Sale Made?" and each cell would have either Y or N in it. I then want the last cell in that column to show the percentage of Ys. So, If C1 was Y, C2 was Y, C3 was N and, C4 was Y, C5 would read "75" or "75%" It seems simple enough, but I can't think of what the hell to do.... any help would be awesome. Thanks 510599[/snapback] I've done some pretty complicated things on excel but I don't know about this, seems simple but maybe its how you explained it that confused me. Link to comment Share on other sites More sharing options...
stevestojan Posted November 22, 2005 Author Share Posted November 22, 2005 I've done some pretty complicated things on excel but I don't know about this, seems simple but maybe its how you explained it that confused me. 510606[/snapback] Yeah, it doesn't seem hard, and maybe I didn't explain it well... let me try again: Say you have a column to show the Bills' winning percentage. Its a simple one column spreadsheet and each cell has either 'W' or 'L' in it.... I want the cell at the bottom to figure out what percentage of the games are WINS, or W's.... So, The column would look something like this: W L W L W L 50% I know it should be a simple 'if' formula, but I can't figure it out... Link to comment Share on other sites More sharing options...
linksfiend Posted November 23, 2005 Share Posted November 23, 2005 Go to help and learn about the COUNTIF function. Something like =COUNTIF(range,"Y")/(COUNTIF(range,"Y")+COUNTIF(range,"N")) should work Of course, this doesn't help you if someone enters YES or NO or anything else besides Y or N exacly Link to comment Share on other sites More sharing options...
UB2SF Posted November 23, 2005 Share Posted November 23, 2005 If I was doing this, I would use numbers instead of letters. If you used 1 for "Y" and 0 for "N", then your final column could simply SUM the 1s and 0s in the Y/N columns and divide by the total number of Y/N columns. For example, if the Y/N numbers were in columns C through N (12 columns) in row 2, then your forumla would be: =sum(C2:N2)/12 If you don't go numeric, then I can think of two other options: 1) Use Y/Ns, but then run a pivot table to gather and analyze the results. Pivot tables RULE!!! 2) Use nested if/then statements to convert the letters to numbers (for example, if <cell>="Y", then 1)... but I would avoid this because it will get very complex. Good luck. Link to comment Share on other sites More sharing options...
Corp000085 Posted November 23, 2005 Share Posted November 23, 2005 Yeah, it doesn't seem hard, and maybe I didn't explain it well... let me try again: Say you have a column to show the Bills' winning percentage. Its a simple one column spreadsheet and each cell has either 'W' or 'L' in it.... I want the cell at the bottom to figure out what percentage of the games are WINS, or W's.... So, The column would look something like this: W L W L W L 50% I know it should be a simple 'if' formula, but I can't figure it out... 510618[/snapback] W L L L W W L W L 0.400 get it right man... we're not at 500 yet!! as for the excel problem, i have no clue... i only use numbers and the data analysis package, although i do know its possible to tally letters. good luck and post your results. Link to comment Share on other sites More sharing options...
Matt in KC Posted November 23, 2005 Share Posted November 23, 2005 Go to help and learn about the COUNTIF function. Something like =COUNTIF(range,"Y")/(COUNTIF(range,"Y")+COUNTIF(range,"N")) should work Of course, this doesn't help you if someone enters YES or NO or anything else besides Y or N exacly 510620[/snapback] If I was doing this, I would use numbers instead of letters. If you used 1 for "Y" and 0 for "N", then your final column could simply SUM the 1s and 0s in the Y/N columns and divide by the total number of Y/N columns. For example, if the Y/N numbers were in columns C through N (12 columns) in row 2, then your forumla would be: =sum(C2:N2)/12 If you don't go numeric, then I can think of two other options: 1) Use Y/Ns, but then run a pivot table to gather and analyze the results. Pivot tables RULE!!! 2) Use nested if/then statements to convert the letters to numbers (for example, if <cell>="Y", then 1)... but I would avoid this because it will get very complex. Good luck. 510624[/snapback] I'd use a cross of these two suggestions. 1 Add another column to the right of your Y/N column (hide it late if you don't want it to be visible) 2 Add the following formula to the top cell in that column - - in my example A4 is the top of the Y/N column and B4 is whree I'm typing: =IF(A4="Y",1,0) 3 Add an average to the bottom of this new column: =AVERAGE(B4:B13) Link to comment Share on other sites More sharing options...
meazza Posted November 23, 2005 Share Posted November 23, 2005 Yeah, it doesn't seem hard, and maybe I didn't explain it well... let me try again: Say you have a column to show the Bills' winning percentage. Its a simple one column spreadsheet and each cell has either 'W' or 'L' in it.... I want the cell at the bottom to figure out what percentage of the games are WINS, or W's.... So, The column would look something like this: W L W L W L 50% I know it should be a simple 'if' formula, but I can't figure it out... 510618[/snapback] I'm sorry, I tried but i can't think of anything... all I could suggest is making W = 1 and making L = 0 and taking the total and dividing it by the summation. I dont' know if this helps good luck Link to comment Share on other sites More sharing options...
nick in* england Posted November 23, 2005 Share Posted November 23, 2005 SIMPLE!!! THE COUNTIF command is what you need. A1 Y A2 N A3 Y A4 N A5 Y A6 N A7 N A8 N A9 Y A10 N A11 =COUNTIF(A1:A10,"Y")/ROWS(A1:A10) The COUNTIF command returns the number of cells between A1 and A10 that have the value "Y". It then divides the total by the number of Rows in the range A1 to A10. Format A11 to display as a percentage and you are done. Link to comment Share on other sites More sharing options...
nick in* england Posted November 23, 2005 Share Posted November 23, 2005 Also - try F1 in Excel... =COUNTIF(B2:B7,"Yes") / (ROWS(B2:B7) -COUNTIF(B2:B7, "<>"&"*")) The average number of Yes votes excluding blank cells in the second column above formatted as a percentage with no decimal places (50%) Link to comment Share on other sites More sharing options...
Recommended Posts