Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Example: Under column B

row 10: mop
row 11: top
row 12: cop
row 13: mop

In another cell in another column, I'd like to know the percentage of the word "mop" (50%) and another cell would be the percentage of the occurrence of "top" (25%), etc. More rows will be added as needed and the data in column B does not start until row 10. Thanks.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Assuming the words are in column B and start at row 10 and you want to be able to fill the sheet to row 65536 (the maximum):

To determine the percentage for "mop":

=COUNTIF($B$10:$B$65536,"mop")/COUNTA($B$10:$B$65536)

For "top":

=COUNTIF($B$10:$B$65536,"top")/COUNTA($B$10:$B$65536)

And so on.

If there is nothing in column B in rows 1-9 then you can just use $B:$B instead of $B$10:$B$65536

share|improve this answer

Copy and paste the list using an advanced filter to copy just the distinct values. Copy these values to column B.

In column C create a formula that uses COUNTIF that looks a the the A column values and compares it to the value in the same row B column. Something like: =COUNTIF(A$10:A$13,C10)

Somewhere else use COUNTA or a similar function to count the number of rows in column A with a value.

In column D create a formula that divides the column C count for each word by the total count value to give you a percentage.

share|improve this answer
    
The answer given does not account for column A changing by entries being added. I want it to be dynamic. –  MattUebel Mar 2 '11 at 1:19
    
Add a larger range than A$13, you could go to the max if you wanted. –  jzd Mar 2 '11 at 3:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.