count number of column's with Yes IF column A is a 1

[Steve]

Hello All

I hope you can help. I have very limited knowledge of excel so treat
me as an idiot please....

This is my table so far:

1 yes no yes no
2 No Yes No Yes
2 yes no yes no
2 No No No No
3 No Yes No No
1 yes no yes no
1 Yes 0 0 0
5 Yes No 0 No
2 yes no yes no
1 0 0 0 0
3 Yes No No No
4 yes no yes no
5 yes no yes no

I want a formula that will give me the PERCENTAGE of yes's in a
particular row IF column A is a 1.

Many thanks

STeve

 

[Dave Peterson]

Maybe...

=if(a1<>1,"not 1!",countif(b1:e1,"yes")/counta(b1:e1))

 

[Pete_UK]

Put this in F1:

=IF(A1=1,COUNTIF(B1:E1,"Yes")/COUNTIF(B1:E1,"<>0"),"")

Format the cell as percentage, then copy down for as many rows as you
have.

Hope this helps.

Pete

 

[Steve]

Put this in F1:

=IF(A1=1,COUNTIF(B1:E1,"Yes")/COUNTIF(B1:E1,"<>0"),"")

Format the cell as percentage, then copy down for as many rows as you
have.

Hope this helps.

Pete









- Show quoted text -

That works great!

Now my next problem ........ For my whole table (which is fixed in
size about 50 rows) i want to know the persentages of Yes in a row for
ALL rows IF there is 1 in column A,

 

[Ken Johnson]

That works great!

Now my next problem ........ For my whole table (which is fixed in
size about 50 rows) i want to know the persentages of Yes in a row for
ALL rows IF there is 1 in column A,

If you are wanting the percentage based on...

( "yes" count in rows with 1 in column A)/(cell count in rows with 1
in column A)

=5/16 = 31.25% in supplied data

then maybe...

=SUM((A1:A13=1)*(B1:E13="yes"))/(COUNTIF(A1:A13,1)*COLUMNS(B1:E13))

which is an array formula and so must be committed with Ctrl+Shift
+Enter.

If you are wanting the percentage based on ...

( "yes" count in rows with 1 in column A)/(cell count in table)

=5/52 = 9.62% in supplied data

then maybe...

=SUM((A1:A13=1)*(B1:E13="yes"))/(ROWS(B1:E13)*COLUMNS(B1:E13))

also an array formula and so must be committed with Ctrl+Shift+Enter.


Ken Johnson