EXCEL: Distribution Frequency Function

2 Comments

Many of my clients has asked me “How do I count the number of occurrence for a particular item?”  For example, how many answered with a value of 6 for question number 12 on my survey. Therefore, if a survey was passed out among a group of people, can Excel tally the score for each question? The answer of course is Yes! The Help screen from Excel provides the following definition and after you read it, you are probably still confused.

The FREQUENCY function returns a frequency distribution as a vertical array. For a given set of values and a given set of bins (or intervals), a frequency distribution counts the number of values that occur in each interval. 

The FREQUENCY function syntax is as follows: 
   FREQUENCY(data_array, bins_array)

The function returns the number of elements you specify in the data_array argument that fall within the intervals you specify in the bins_array argument.

Let me explain to you in laymen term: Here are the simply steps!!

First, the values you want to tally must be in a number format. If a value is in text form like Yes or No, assign Yes to 1 and assign No to 2. Create a table that represent the questions across the columns and each answer from each survey collected.

Picture1
Second, create a column call Bins where the number represents the values of the choices given on the survey.  Across the headings, copy the headings of the questions.

Picture2
Third, you create the frequency formula in the first cell right of the first bin number.

1. Highlight the empty column next to the Bins column, not including the column heading.

Picture4
2. Type the following formula as shown below

Picture5

  • A2:A11 represents the first column of choices (type in or highlight A2 through A11)
  • the second part of the formula represents the Bins(type in or highlight J2 through J14) then F4 key (absolute key) to lock in the range of cells.
  • YOU MUST HIT CTRL+SHIFT+ENTER to complete the formula as an ARRAY.

Picture6

Lastly, fill the formula across and you have a table that counts the number of occurrences for each question.

Picture7

Have Fun!

Advertisements

WORD: Even/Odd Pages along with Title and Preface Page

Leave a comment

I have created a document that can be used that contains:

  • Ttile Page without headers or footers
  • Blank Page without headers or footers
  • Preface pages that contains roman numerals
  • Pages where nonsense that you will delete and begin typing your information.
  • The document is set up for even and odd pages.

Go to the following link:

http://sdrv.ms/ODX9U7

and do the following.

  1. Click on the file called “Section Break wuth Even and Odd and Preface Page”
  2. Click on File
  3. Click on Save As
  4. Click on Download Icon
  5. Click on the document that is displayed above the task bar
  6. Click on Enable Editing
  7. File Save As to your file folder

Enjoy!

WORD and EXCEL: Use Keyboard Keys to Navigate Thorough the Print Preview

Leave a comment

When you are ready to print, I recommend to go to the Print Preview from the File command in order to save paper.  I navigate through the document or the spreadsheet by using the keys on the keyboard.

In Excel, use the PgUp or PgDn or the arrow keys to navigate through each page.  If you want to go immediately to end of the spreadsheet, just hit the END key and of course to go to the beginning of the spreadsheet, simply hit the Home key.

In Word, ONLY PgUp and PgDn allows you to navigate through each page.  Ctrl+Home and Ctrl+End allows you to go directly to the beginning and to the end of the document.

Remember, you need to activate the keys by clicking on the right side of the print preview.