Need to Analyze Word Density

[newbieexceldude]

Hello everyone,

I need some assistance please. I need a formula for calculating the density of words in a column. I'm thinking this would involve some sort of VLOOKUP.

It will need to count every instance of each keyword and every phrase variation. Then it will determine the density of each one.

For example: "Totally Free DVD Players" This is the reference, it would be column A

Word Counts would be in Column B

1 Word Counts
Totally
Free
DVD
Players

2 Word Counts
Totally Free
Free DVD
DVD Players

3 Word Counts
Totally Free DVD
Free DVD Players

4 Word Counts
Totally Free DVD Players

Let's assume that there are 100 cells from A 2 - A 101. The formula would need to capture somehow every word in the spreadsheet, reference against column A, and determine the density for every phrase starting with 1 word.

Example: The title of a Web page is 'Get Best XYZ Services'. Keyword Density for 'XYZ services' is 2*1/4*100%=50%. If you reduce the number of words in the title by removing the word 'get', so the title becomes 'Best XYZ Services', than the keyword weight will be larger: 2*1/3*100%=67%

Finally, I need to get an average for each density in the spreadsheet. I'd like this summary report to be created in a new worksheet.

I have link to a webpage that helps explain it better: http://www.web1marketing.com/blog/in...sity-in-excel/

Well, I guess that's it. Thanks in advance for any suggestions

[stanleydgromjr]

newbieexceldude,

Please post your workbook (or sample) - scroll down and see "Manage Attachments".


I think this is cross posted:
http://www.mrexcel.com/forum/showthread.php?t=397738

[martindwilson]

yep its a cross post tut tut no link.
formula for calculating density in one cell is
=(LEN(TRIM(E1))-LEN(SUBSTITUTE(E1," ",""))+1)/(LEN(TRIM($A1))-LEN(SUBSTITUTE($A1," ",""))+1) wher e1 contians key word/words and a1 the sentence ,format cell %

[Leith Ross]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Cross posted here: http://www.mrexcel.com/forum/showthread.php?t=397738