Group:  Microsoft Excel » microsoft.public.excel.worksheet.functions
Thread: need formula for % of times text appears in row.

DotNetBag
.NET Development Newsgroups

HTVi
TV Discussion Newsgroups

 Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Rising Antivirus 2006

need formula for % of times text appears in row.
sunslight 29.09.2006 18:51:02
Hi. I need help with with calculating the percentage of times
a specific text appears within a row.
The formula needs to be relative, so I can copy and paste it
into successive rows.

e.g. Based on the text in A2:D2, what are the percentages
for “x,” “y,” “z” in that range?

____________________________________

__ |_A_|_B_|_C_|_D_| _E_ |__F_ |_ G_ |__
1 | | | | | %x | %y | %z |

2 | z | y | z | x | 25 | 25 | 50 |

_____________________________________


(Thus, row 2 is comprised of 25% X, 25% Y, 50% Z.
Can someone help me with this?)

Thank you,
Bob
RE: need formula for % of times text appears in row.
Toppers 29.09.2006 19:36:02
Based on your example:

in E2:

=COUNTIF($A2:$D2,E1)/COUNTA($A2:$D2) where E1="x"

Copy across to cells F2 & G2 and copy down as required

Cells are formatted as %.

HTH

"sunslight" wrote:

[Quoted Text]
> Hi. I need help with with calculating the percentage of times
> a specific text appears within a row.
> The formula needs to be relative, so I can copy and paste it
> into successive rows.
>
> e.g. Based on the text in A2:D2, what are the percentages
> for “x,” “y,” “z” in that range?
>
> ____________________________________
>
> __ |_A_|_B_|_C_|_D_| _E_ |__F_ |_ G_ |__
> 1 | | | | | %x | %y | %z |
>
> 2 | z | y | z | x | 25 | 25 | 50 |
>
> _____________________________________
>
>
> (Thus, row 2 is comprised of 25% X, 25% Y, 50% Z.
> Can someone help me with this?)
>
> Thank you,
> Bob
>
>
RE: need formula for % of times text appears in row.
Marcelo 29.09.2006 19:47:02
Hi

=contif($a2:$d2,right(e1,1))/counta($a2:$d2)

copy it right and down as you need

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"sunslight" escreveu:

[Quoted Text]
> Hi. I need help with with calculating the percentage of times
> a specific text appears within a row.
> The formula needs to be relative, so I can copy and paste it
> into successive rows.
>
> e.g. Based on the text in A2:D2, what are the percentages
> for “x,” “y,” “z” in that range?
>
> ____________________________________
>
> __ |_A_|_B_|_C_|_D_| _E_ |__F_ |_ G_ |__
> 1 | | | | | %x | %y | %z |
>
> 2 | z | y | z | x | 25 | 25 | 50 |
>
> _____________________________________
>
>
> (Thus, row 2 is comprised of 25% X, 25% Y, 50% Z.
> Can someone help me with this?)
>
> Thank you,
> Bob
>
>

Home | Search | Terms | Imprint | Contact
Newsgroups Reader - provided by WiredBox.Net