Loadingge9

How to Add Analysis ToolPak in Excel 2016 for Mac? To install Analysis toolpak in Excel for Mac, follow below steps. Goto tools or Insert menu. Locate Add-Ins and Click on it. You’ll be prompted to choose from available add-ins. Select Analysis Toolpak. Analysis Toolpak is added. I’ve used XLMiner with Google Sheets to run regressions - its a free add on that has lots of stats power. You can also use Parabola to do stats modeling and data transformation/ETL.

Microsoft Excel offers users hundreds of different functions and formulas for a variety of purposes. Whether you have to analyze your personal finance or any large data set, it's the functions that make the job easy. Also, it saves a lot of time and efforts. However, finding the right function for your data set can be very tricky.

So if you've been struggling to find the appropriate Excel function for data analysis, then you've come to the right place. Here's a list of some essential Microsoft Excel functions that you can use for data analysis and you can boost your productivity in the process.

Note: For the uninitiated, the functions mentioned in this post need to be added in the Formula Bar of an Excel spreadsheet containing data or within the cell in which you want the result.

Also on Guiding Tech

How to Create Free Surveys in Microsoft Excel Online

Read More

1. CONCATENATE

=CONCATENATE is one of the most crucial functions for data analysis as it allows you to combine text, numbers, dates, etc. from multiple cells into one. The function is particularly useful for combining data from different cells into a single cell. For instance, it comes handy for creating the tracking parameters for marketing campaigns, build API queries, add text to a number format, and several other things.

In the example above, I wanted the month and sales together in a single column. For that, I have used the formula =CONCATENATE(A2, B2) in the cell C2 to get Jan$700 as the result.

Formula: =CONCATENATE(cells you want to combine)

2. LEN

=LEN is another handy function for data analysis that essentially outputs the number of characters in any given cell. The function is predominantly usable while creating title tags or descriptions that have a character limit. It can also be useful when you're trying to find out the differences between different unique identifiers which are often quite lengthy and not in the correct order.

In the example above, I wanted to count the figures for the number of views I was getting each month. For this, I made use of the formula =LEN(C2) in the cell D2 to get 5 as the result.

Formula: =LEN(cell)

3. VLOOKUP

=VLOOKUP is probably one of the most recognizable functions for anyone familiar with data analysis. You can use it to match data from a table with an input value. The function offers two modes of matching — exact and approximate, which is controlled by the range of the lookup. If you set the range to FALSE, it'll look for an exact match, but if you set it to TRUE, it'll look for an approximate match.

In the example above, I wanted to look up the number of views in a particular month. For that, I used the formula =VLOOKUP('Jun', A2:C13, 3) in the cell G4 and I got 74992 as the result. Here, 'Jun' is the lookup value, A2:C13 is the table array in which I'm looking for 'Jun' and 3 is the number of the column in which the formula will find the corresponding views for June.

The only downside of using this function is that it only works with data that has been arranged into columns, hence the name — vertical lookup. So if you've got your data arranged in rows, you'll first need to transpose the rows into columns.

Formula: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

4. INDEX/MATCH

Much like the VLOOKUP function, the INDEX and MATCH function come in handy for searching specific data based on an input value. The INDEX and MATCH, when used together, can overcome the VLOOKUP's limitations of delivering the wrong results (if you are not careful). So when you combine these two functions, they can pinpoint the data reference and search for a value in a single dimension array. That returns the coordinates of the data as a number.

In the example above, I wanted to look up the number of views in January. For that, I used the formula =INDEX (A2:C13, MATCH('Jan', A2:A13,0), 3). Here, A2:C13 is the column of data I want the formula to return, 'Jan' is the value I want to match, A2:A13 is the column in which the formula will find 'Jan' and the 0 signifies that I want the formula to find an exact match for the value.

If you want to find an approximate match you will have to substitute the 0 with 1 or -1. So that the 1 will find the largest value less than or equal to the lookup value and -1 will find the smallest value less than or equal to the lookup value. Do note that if you don't use 0, 1, or -1, the formula will use 1, by.

Now if you don't want to hardcode the name of the month, you can replace it with the cell number. So we can replace 'Jan' in the formula mentioned above with F3 or A2 to get the same result.

Formula: =INDEX(column of the data you want to return, MATCH(common data point you're trying to match, column of the other data source that has the common data point, 0))

5. MINIFS/MAXIFS

=MINIFS and =MAXIFS are very similar to the =MIN and =MAX functions, except for the fact that they allow you to take the minimum/maximum set of values and match them on particular criteria as well. So essentially, the function looks for the minimum/maximum values and matches it with input criteria.

In the example above, I wanted to find the minimum scores based on the student's gender. For that, I used the formula =MINIFS (C2:C10, B2:B10, 'M') and I got the result 27. Here C2:C10 is the column in which the formula will look for the scores, B2:B10 is a column in which the formula will look for the criteria (the gender), and 'M' is the criteria.

Similarly, for maximum scores, I used the formula =MAXIFS(C2:C10, B2:B10, 'M') and got the result 100.

Formula for MINIFS: =MINIFS(min_range, criteria_range1, criteria1,...)

Formula for MAXIFS: =MAXIFS(max_range, criteria_range1, criteria1,...)

Also on Guiding Tech

How to Use the Filter Data Tool in MS Excel

Read More

6. AVERAGEIFS

The =AVERAGEIFS function allows you to find an average for a particular data set based on one or more criteria. While using this function, you should keep in mind that each criteria and average range can be different. However, in the =AVERAGEIF function, both the criteria range and sum range need to have the same size range. Notice the difference of singular and plural between these functions? Well, that's where you need to be careful.

In this example, I wanted to find the average score based on the gender of the students. For that I used the formula, =AVERAGEIFS(C2:C10, B2:B10, 'M') and got 56.8 as the result. Here, C2:C10 is the range in which the formula will look for the average, B2:B10 is the criteria range, and 'M' is the criteria.

Formula: =AVERAGEIFS(average_range, criteria_range1, criteria1,...)

7. COUNTIFS

Now if you want to count the number of instances a data set meets specific criteria, you'll need to use the =COUNTIFS function. This function allows you to add limitless criteria to your query, and thereby makes it the easiest way to find the count based on the input criteria.

In this example, I wanted to find the number of male or female students who got passing marks (i.e. >=40). For that I used the formula =COUNTIFS(B2:B10, 'M', C2:C10, '>=40'). Here, B2:B10 is the range in which the formula will look for the first criteria (gender), 'M' is the first criteria, C2:C10 is the range in which the formula will look for the second criteria (marks), and '>=40' is the second criteria.

Formula: =COUNTIFS(criteria_range1, criteria1,...)

8. SUMPRODUCT

The =SUMPRODUCT function helps you multiply ranges or arrays together and then returns the sum of the products. It's quite a versatile function and can be used to count and sum arrays like COUNTIFS or SUMIFS, but with added flexibility. You can also use other functions within SUMPRODUCT to extend its functionality even further.

In this example, I wanted to find the sum total of all the products sold. For that, I used the formula =SUMPRODUCT(B2:B8, C2:C8). Here, B2:B8 is the first array (the quantity of products sold) and C2:C8 is the second array (the price of each product). The formula then multiples the quantity of each product sold with its price and then adds all of it up to deliver the total sales.

Formula: =SUMPRODUCT(array1, [array2], [array3],...)

9. TRIM

The =TRIM function is particularly useful when you're working with a data set that has several spaces or unwanted characters. The function allows you to remove these spaces or characters from your data with ease, allowing you to get accurate results while using other functions.

In this example, I wanted to remove all the extra spaces between the words Mouse and pad in A7. For that I used the formula =TRIM(A7).

The formula simply removed the extra spaces and delivered the result Mouse pad with a single space.

Formula: =TRIM(text)

10. FIND/SEARCH

Rounding things off are the FIND/SEARCH functions which will help you isolate specific text within a data set. Both the functions are quite similar in what they do, except for one major difference — the =FIND function only returns case-sensitive matches. Meanwhile, the =SEARCH function has no such limitations. These functions are particularly useful when looking for anomalies or unique identifiers.

In this example, I wanted to find the number of times 'Gui' appeared within Guiding Tech for which I used the formula =FIND(A2, B2), which delivered the result 1. Now if I wanted to find the number of times 'gui' appeared within Guiding Tech instead, I'd have to use the =SEARCH formula because it isn't case sensitive.

Formula for Find: =FIND(find_text, within_text, [start_num])

Formula for Search: =SEARCH(find_text, within_text, [start_num])

Also on Guiding Tech

#office 365

Click here to see our office 365 articles page

Master the Data Analysis

These essential Microsoft Excel functions will definitely help you with data analysis, but this list only scratches the tip of the iceberg. Excel also includes several other advanced functions to achieve specific results. If you're interested in learning more about those functions, let us know in the comments section below.

Next up: If you wish to use Excel more efficiently, then you should check out the next article for some handy Excel navigation shortcuts that you must know.

The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.Read NextThe Complete List of MS Excel Function Key (F1 to F12) ShortcutsAlso See#msexcel #productivity

Did You Know

As of March 2020, Microsoft Teams has over 75 million daily active users.

More in Windows

Top 3 Fixes for Camera Not Working on Windows 10 PC

Microsoft’s spreadsheet application known as Excel, found in Microsoft Office, features graphing tools, calculation capabilities, pivot tables and many other helpful tools for its users. In addition to these default features, the user may choose to incorporate various add-ins offered by Microsoft at no cost. The add-in we are discussing today is the Analysis ToolPak which performs complex statistical and engineering analyses.

This particular Excel add-in is easy to install and use and saves the user time in performing complex analyses. Because these functions are quite complex and not needed by the average Excel user, they are not found in the default version of the program and it is necessary for the user to add them on his or herself.

Excel’s Data Analysis ToolPak for Mac is easy to install:

1. Click the “Tools” tab at the top of the screen while in Excel.
2. Then click “Add-Ins”
3. Finally, check the box that says “Analysis ToolPak” then “OK”

The program will then take a few seconds to add this feature to Excel. After it is done loading, the user can access the Data Analysis ToolPak by clicking the “Tools” tab again at the top of the screen and then clicking “Data Analysis”, which now appears at the bottom of this pull-down menu.

After accessing Data Analysis, a box pops up with the various analysis tools. The next section will describe the various tools that the Data Analysis ToolPak has to offer.

Functions of the Analysis ToolPak

There are 19 different functions found within this feature. We won’t get into every one, as a few of them are subsets of more general functions.

• The Anovaanalysis tools (single factor, two-factor with replication, two-factor without replication) provide different types of variance analysis. Anova is used to see if there is any difference between groups of some variable.
• The Correlation worksheet calculates the correlation coefficient between two measurement variables when measurements on each variable are observed for each of N subjects. This tool examines a pair or measurement variables and determines whether they tend to move together, or correlate.
• The Covariance tool can be used in the same setting as Correlation when you have N different measurement variables observed on a set of individuals, and like Correlation, it will give you an output table (matrix) that illustrates the covariance between the two variables.
• The Descriptive Statistics tool generates a report of univariate statistics for data in the input range, providing information about the central tendency and variability of the data.
• Exponential Smoothing predicts a value that is based on the forecast for the prior period, adjusted for the error in that prior forecast. It uses the smoothing constant a, the magnitude of which determines how strongly the forecasts respond to errors in the prior forecast.
• The F-Test Two-Sample for Variances analysis tool performs a two-sample F-test to compare two population variances.
• The Fourier Analysis tool solves problems in linear systems and analyzes periodic data by using the Fast Fourier Transform (FFT) method to transform data.
• The Histogram analysis tool calculates individual and cumulative frequencies for a cell range of data and data bins. This tool generates data for the number of occurrences of a value in a data set.
• The Moving Average analysis tool projects values in a forecast period, based on the average value of the variable over a specific number of preceding periods. A moving average provides trend information that a simple average of all historical data would mask. This tool might be used to forecast sales, inventory, or other trends. Each forecast value is based on the following formula.
• The Random Number Generation analysis tool fills a range with independent random numbers that are drawn from one of several distributions and you can characterize the subjects in a population with a probability distribution.
• The Rank and Percentile analysis tool produces a table that contains the ordinal and percentage rank of each value in a data set. You can analyze the relative standing of values in a data set.
• The Regression analysis tool performs linear regression analysis by using the “least squares” method to fit a line through a set of observations. You can analyze how a single dependent variable is affected by the values of one or more independent variables.
• The Sampling analysis tool creates a sample from a population by treating the input range as a population. When the population is too large to process or chart, you can use a representative sample. You can also create a sample that contains only the values from a particular part of a cycle if you believe that the input data is periodic.
• The t-Test analysis tools test for equality of the population means that underlie each sample. The three sub features of this tool (paired two sample for means, two-sample assuming equal variances, two-sample assuming unequal variances) employ different assumptions: that the population variances are equal, that the population variances are not equal, and that the two samples represent before-treatment and after-treatment observations on the same subjects.
• Finally, the z-Test tool performs a two sample z-Test for means with known variances. This tool is used to test the null hypothesis that there is no difference between two population means against either one-sided or two-sided alternative hypotheses.

Excel For Mac Data Analysis Toolpak

What do These Tools Look Like in Use?

Here’s a few screenshots to show some of these handy tools in action.

Above is an example of how the Anova feature would work.

And this is the correlation function in use.

Finally, we have the rank and percentile tool pictured. Here, a teacher has put her students’ grades into the program and their grade (percentile) has been calculated from the data given.

How To Get Data Analysis On Excel

While MS Excel for Mac can be utilized very effectively for relatively simple tasks, it can also perform very specific, complex analysis functions with the Data Analysis ToolPak add-in. Depending on how you need to analyze certain data and variables, Excel can accommodate even the most demanding users such as engineers, statisticians and business owners, among many others.