Skip to main content

Data Analysis Tool Pack


 

The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial, statistical and engineering data analysis.

To load the Analysis ToolPak add-in, execute the following steps.

1. On the File tab, click Options.


2. Under Add-ins, select Analysis ToolPak and click on the Go button.

Click Analysis ToolPak

3. Check Analysis ToolPak and click on OK.

Check Analysis ToolPak

4. On the Data tab, in the Analysis group, you can now click on Data Analysis.

Click Data Analysis

The following dialog box below appears.

5. For example, select Histogram and click OK to create a Histogram in Excel.

Select Histogram



Example Rank and Percentile

The Rank and Percentile contained within the Analysis-ToolPak can be quickly used to find the rank of all the values in a list.
The advantage of using the Rank and Percentile feature is that the percentile is also added to the output table.
The percentile is a percentage that indicates the proportion of the list which is below a given number.


Highlight the list (or the cells) which you want the corresponding of. In this case cells "B2:B15".
Select (Tools > Data Analysis) and select "Rank and Percentile" from the list.

Input Range - This is the cell reference for the list of numbers (or range of data) you want to analyse.
Grouped By - This indicates whether the input range is arranged by columns or by rows. In this case the data is arranged in a single column.
Labels in First Row - If your data is arranged in columns the first row of your data can contains labels. This checkbox needs to be ticked if the first row does contain labels. These labels will be transferred to the output table automatically if specified. In this case we do have a label in the first row.
Output Range - This is the upper-left cell for the output table. Excel generates one output table for each column or row of data. In this case we only have a single column so we will only get one output table.
New Worksheet Ply - Click to insert a new worksheet in the current workbook and paste the results starting at cell A1 of the new worksheet. To name the new worksheet, type a name in the box.
New Workbook - Click to create a new workbook and paste the results on a new worksheet in the new workbook.


Each output table contains four columns:
Point - The location of the value within the original list. This can be used to quickly sort the output table into the same order of the original list.
Original - This is the column containing the original values. This column has the same column name as the original list since we used labels in the first row.
Rank - This is the rank of the corresponding number in the list.
Percent - This is the numbers percentage rank within the list. This percentage indicates the proportion of the list which are below this given number.

Labels:

Comments

Post a Comment

Popular posts from this blog

Up and Down Markers using Conditional Formatting

There is a super-obscure way to add up/down markers to a pivot table to indicate an increase or a decrease. Somewhere outside the pivot table, add columns to show increases or decreases. In the figure below, the difference between I6 and H6 is 3, but you just want to record this as a positive change. Use  SIGN(I6-H6)  to get either +1, 0, or -1. Select the two-column range showing the sign of the change and then select Home, Conditional Formatting, Icon Sets, 3 Triangles. (I have no idea why Microsoft called this option 3 Triangles, when it is clearly 2 Triangles and a Dash, as shown below.) With the same range selected, now select Home, Conditional Formatting, Manage Rules, Edit Rule. Check the Show Icon Only checkbox. With the same range selected, press  Ctrl+C  to copy. Select the first Tuesday cell in the pivot table. From the Home tab, open the Paste dropdown and choose Linked Picture. Excel pastes a live picture of the icons above the table. At this point, adju...
Post a Comment
Read more

Indirect Function

INDIRECT  is pretty cool for grabbing a value from a cell. Can  INDIRECT  point to a multi-cell range and be used in a  VLOOKUP  or  SUMIF  function?  You can build an  INDIRECT  function that points to a range. The range might be used as the lookup table in a  VLOOKUP  or as a range in  SUMIF  or  COUNTIF . In  Figure , the formula pulls data from the worksheets specified in row 4. The second argument in the  SUMIF  function looks for records that match a certain date from column A. Note:  Because each worksheet might have a different number of records, I chose to have each range extend to 300. This is a number that is sufficiently larger than the number of transactions on any sheet. The formula in cell B5 is: =SUMIF(INDIRECT(B$4&"!A2:A300"), $A5, INDIRECT(B$4&"!C2:C300")) Summary:  You can use  INDIRECT  to grab data from a multi-cell range.
Post a Comment
Read more

Create Sum that gives summary of all Worksheets in Excel

  You have a workbook with 12 worksheets, 1 for each month. All of the worksheets have the same number of rows and columns. You want a summary worksheet in order to total January through December. To create it, use the formula  =SUM(January:December!B4) . Copy the formula to all cells and you will have a summary of the other 12 worksheets. Caution I make sure to never put spaces in my worksheet names. If you do use spaces, the formula would have to include apostrophes, like this:  =SUM('Jan 2018:Mar 2018'!B4) .
Post a Comment
Read more