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.

Comments

Popular posts from this blog

Diploma In Data Analytics with Excel and Power BI

I will Be teaching the below mentioned course in association with Sanyukta Trust Sanyukta Trust is proud to introduce a special course designed to transform you into Data Analytics Expert! In this course, you will benefit by learning how to use MS Excel and Power Bi (Business Intelligence) to read, clean, transform, visualize and analyze the data.  Join our AIBTE (All India Board of Technical Education) Government recognised Online course of Diploma in Data Analytics with Microsoft Excel and Power Business Intelligence (Bi) Session: 16 sessions plus assignment submissions  Course Highlights: - Introduction to Data Analytics & MS Office Tools - In depth knowledge of MS Excel & Power Bi - Clean and Transform data with Power Query - Data Modelling with Dax/Power Pivot  - 3D maps  - Interactive Charts and Data Visualization with Slicers - Interactive Dashboards Days: Twice a Week - Saturday - 5pm to 6.30pm - Sunday - 11am to 12.30pm ...

20 Time Intelligence Dax Measures

20 Time Intelligence DAX measures in Power BI with examples: Year-to-Date Sales: css Copy code YTD Sales = TOTALYTD( [Total Sales] , Calendar [Date] ) Month-to-Date Sales: css Copy code MTD Sales = TOTALMTD( [Total Sales] , Calendar [Date] ) Quarter-to-Date Sales: css Copy code QTD Sales = TOTALQTD( [Total Sales] , Calendar [Date] ) Previous Year Sales: mathematica Copy code Previous Year Sales = CALCULATE ( [ Total Sales ] , SAMEPERIODLASTYEAR ( Calendar [ Date ] ) ) Year-over-Year Growth: css Copy code YoY Growth = DIVIDE( [Total Sales] - [Previous Year Sales] , [Previous Year Sales] ) Rolling 3-Month Average Sales: sql Copy code 3 M Rolling Avg Sales = AVERAGEX(DATESINPERIOD(Calendar[ Date ], MAX (Calendar[ Date ]), -3 , MONTH ), [Total Sales]) Cumulative Sales: scss Copy code Cumulative Sales = SUMX (FILTER(ALL(Calendar), Calendar [Date] <= MAX (Calendar[Date])), [Total Sales] ) Running Total Sales: scss Copy code Running Total Sales = SUMX (FILTER(ALL(Calendar), Calen...

Goal Seek feature in Excel

Have you ever found yourself plugging in successively higher and lower values into an input cell, hoping to arrive at a certain answer? A tool that is built in to Excel does exactly this set of steps. Select the cell with the Payment formula. On the Data tab, in the Data Tools group, look for the What-If Analysis dropdown and choose Goal Seek…. The figure below shows how you can try to set the payment in B5 to $425 by changing cell B1 Goal Seek finds the correct answer within a second. Note that the formula in B5 stays intact. The only thing that changes is the input value typed in B1. Also, with Goal Seek, you are free to experiment with changing other input cells. You can still get the $425 loan payment and the $25,995 car if your banker will offer you a 71.3379-month loan!