Homework 6: Charging through Text

• Perform a new analysis of text data.
• Lookup how to use various functions in excel.
• Improve your ability to use countif.

• Please download this worksheet
  • Column A contains all of the words to the poem "Charge of the Light Brigade" by Tennyson.
• The case of the data in column A is mixed.
  • While excel doesn't care, it is probably better for us if it is all either uppercase or lowercase.
  • There is a function that will convert it to either.
  • Find an appropriate text function and convert the words to all lowercase and apply it to change all the words to lower case in column B
  • Find an appropriate text function and convert the words to all uppercase and apply it to change all the words to lower case in column C
  • Finally in column D, compute the length of each word using a function.
  • 
• In column F, find the unique words in the data (use the upper case words in column C). You have a procedure to do this.
• In Column G, compute the frequency of each word. Use countif.
  • 
• The rank function will compute the place in the sorted order for any given value in a list.
  • Read the help section on the rank function and use it to compute the rank for the frequency of each word.
  • 
• Use the sort utility on the Data tab to sort your words by frequency.
  • 
• In J1:K8, Construct a frequency analysis of the word count
  • Column J should be hard coded (enter the values)
  • Column K should be a countif of column G and column j
  • 
• Copy the top 7 words from F1:H8 to J12:L19
  • Paste values, not formulas.
  • 
• Go to sheet2 and answer the questions.

The final worksheet

Point Distribution

Submit