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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve - 01 Dec 2007 20:45 GMT

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

Reply to this Message

Dave Peterson - 01 Dec 2007 21:01 GMT

Maybe...

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

> Hello All
>
[quoted text clipped - 23 lines]
>
> STeve

Signature

Dave Peterson

Reply to this Message

Pete_UK - 01 Dec 2007 21:05 GMT

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

> Hello All
>
[quoted text clipped - 23 lines]
>
> STeve

Reply to this Message

Steve - 01 Dec 2007 21:20 GMT

> Put this in F1:
>
[quoted text clipped - 36 lines]
>
> - 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,

Reply to this Message

Ken Johnson - 02 Dec 2007 00:43 GMT

> > Put this in F1:
>
[quoted text clipped - 42 lines]
> 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

Reply to this Message

Rate this thread: