Replace text with Num and do a Percentage?

**dksodhi** · 12-13-2012, 08:22 AM

Hi Guys,

Hoping someone can help.
I am trying to Replace Yes in a Column with Number 1 and No with Number 0 and Keep NA as a nil value.
Once replace is done either in the original column or a new one, I want to be able to get a percentage of Yes vs No

for eg: Column 1 had 2 Yes and 2 No so the % should be 50-50!

Help!

**dilipandey** · 12-13-2012, 08:33 AM

Hi dkshodhi,

Try using below formula:-

Formula:

=COUNTIF($A$1:$A$6,"yes")/COUNTIF($A$1:$A$6,"<>NA")*100&"-"&COUNTIF($A$1:$A$6,"NO")/COUNTIF($A$1:$A$6,"<>NA")*100

See attached : counting text and percentage.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

**dksodhi** · 12-13-2012, 08:53 AM

Hi Dilip,

Thank you...this is what I am looking for however instead of getting the percentage split on just one column, I need it in two columns.

I should have explained it better in my question.
Like B1 says 50% based on all Yes values in A1
and B2 says 50% based on all No values in A1

**dilipandey** · 12-13-2012, 09:05 AM

Okay.. no worries.

If you see into that formula, there is a seperator (-) in between... so just use the left portion in B1 and right portion in B2 with *100

after this,you might need to format the cell as percentage format... thanks

Regards,
DILIpandey

<click on below 'star' if this helps>

**dksodhi** · 12-13-2012, 09:22 AM

Final question...

Is there any way to tell excel to pick the cell by itself instead of defining it from row 1 to 6 etc?
Like It reads everything in Column A...
A:A kind of thing? as I may have data till 6 rows now but it may increase to 20 later?

**dksodhi** · 12-13-2012, 09:19 AM

Works Great!

Thanks

**dilipandey** · 12-13-2012, 09:50 AM

Yes.. for this I would suggest you to look into defined names over internet.. let me know if you face any issue.

Since this thread title is different, would prefer to take that question in another thread with suitable thread title..

Regards,
DILIPandey

<click on below 'star' if this helps>

**dilipandey** · 12-13-2012, 09:51 AM

Yes.. for this I would suggest you to look into defined names over internet.. let me know if you face any issue.

Since this thread title is different, would prefer to take that question in another thread with suitable thread title..

Regards,
DILIPandey

<click on below 'star' if this helps>

**dksodhi** · 12-13-2012, 10:14 AM

Thanks..I will have a look Online.

Replace text with Num and do a Percentage?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Replace text with Num and do a Percentage?

Re: Replace text with Num and do a Percentage?

Re: Replace text with Num and do a Percentage?

Re: Replace text with Num and do a Percentage?

Re: Replace text with Num and do a Percentage?

Re: Replace text with Num and do a Percentage?

Re: Replace text with Num and do a Percentage?

Re: Replace text with Num and do a Percentage?

Re: Replace text with Num and do a Percentage?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions