Excel question....

[stevestojan]

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

[meazza]

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.

[stevestojan]

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

[linksfiend]

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

[UB2SF]

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.

[Corp000085]

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.

[Matt in KC]

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)

[meazza]

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

[nick in* england]

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.

[nick in* england]

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%)