# Trending December 2023 # Calculating Correlation In Different Methods # Suggested January 2024 # Top 20 Popular

You are reading the article Calculating Correlation In Different Methods updated in December 2023 on the website Achiashop.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 Calculating Correlation In Different Methods

Introduction to Correlation Example

The following Correlation examples outline the most common set of Correlation examples:

Start Your Free Investment Banking Course

Example #1

Below, we mention the height and weight of 5 students in a class:

Student Name Height (in cm) Weight (in kgs)

Frank 160 53

Leo 165 61

Jerry 172 74

Celeste 150 51

Tracy 180 82

Based on this simple data on the Height and Weight of students, we can observe a correlation pattern that is positive. The data suggest that weight increases with greater height, and the inference we draw from the correlation implies that taller people usually weigh more.

Example #2

Below, we provide the speeds of different trains and the times they take to reach the same destination.

Train Name Speed (in km/hr) Time Taken (in hrs)

A 40 5

B 50 4

C 80 2.5

Here we can easily observe the Negative Correlation that Speed and Time taken have with the greater the speed of the Train, the fewer hours are taken to reach the destination.

Example of Correlation (With Excel Template)

Let’s take an example to understand the calculation of the correlation in a better manner.

You can download this Correlation Example Excel Template here – Correlation Example Excel Template

Correlation – Example #1

The following table exhibits the returns on ABC Limited’s two stocks in their Model Portfolio for the last 5 years. Based on the same, let us calculate and interpret the correlation between the two stocks.

Solution:

We will use the Pearson Correlation Coefficient to determine the two assets’ correlation. This method, one of the most popular for measuring the linear relationship between two variables, will help us understand the relationship between two stocks: Facebook and Amazon, in our case.

The average Mean for 5 years is calculated as

The standard deviation is calculated as

The Sum of C*D is calculated as

Covariance is calculated as

Correlation is calculated using the given formula below

The Correlation of Negative 0.7501 implies a low to high Negative Correlation between the two stocks.

The correlation discussed in the above example is the Pearson Correlation Coefficient method. It helps measure the linear relationship between the two variables, which in our case was the two stocks in the model portfolio.

Correlation – Example #2

Let’s take another example and understand correlation measures using a popular approach called Spearman Rank Correlation. It is an Ordinal Correlation measure, and correlation calculation is undertaken based on the relationship between the rank of the variables.

Solution:

X is calculated as

The sum of (di^2) is calculated as

The following table exhibits the returns on two stocks, X and Y, for the last 5 months. Based on the same, let us compute and interpret the correlation between the two stocks using the Spearman Rank Correlation method.

Correlation is calculated using the formula given below.

We calculate correlation using the Spearman Correlation method by first ranking the returns of Stock X from lowest to highest in the second column. We then list the returns for Stock Y in the third column for each respective month. The fourth and fifth columns list the return for Stock X and Y rank-wise. The sixth column lists the rankings for each month, and the seventh column determines the sum of squared differences in rankings.

Correlation – Example #3

The following data is observed from the medical reports of 10 patients categorized based on their Age and Blood Pressure Level. Based on the same, let us calculate the Correlation Coefficient.

Solution:

We calculate the sum as follows

We calculate correlation using the formula given below

r = NΣxy – (Σx)(Σy) / √ [NΣx2 – (Σx)2][NΣy2 – (Σy)2]

Conclusion

We encounter examples of correlation in our day-to-day life, and they help us understand the relationship between two things. Researchers often use it for predicting purposes and validating any relationship between different variables. The correlation coefficient varies from -1 to 1 values where -1 indicates a strong Negative Correlation and 1 indicates a strong Positive Correlation between the variables, and a Correlation value of Zero indicates no relationship between the two variables. We have discussed both simple and practical examples above to validate the concept of correlation and its relevance in statistical and other analytical studies.

Recommended Articles

This is a guide to Correlation Example. Here we discuss calculating the correlation using different methods with different examples. You may also look at the following articles to learn more –

You're reading Calculating Correlation In Different Methods

## Searching In Data Structure – Different Search Methods Explained

In today’s rapidly expanding communication network, businesses are going digital to enhance management efficiency. With the increasing amount of data generated on the internet, datasets are becoming more complex. To carefully and efficiently organize, manage, access, and analyze data, utilizing a data structure is crucial. This article focuses on the significance of data structures, exploring the fundamental concept of searching. It delves into various search techniques, including linear and binary search, evaluating their complexities, strengths, and weaknesses.

This article was published as a part of the Data Science Blogathon.

What is a Data Structure?

In computer science, data structures serve as the foundation for abstract data types (ADT), where ADT is the logical form of data types. The physical design of data types is implemented using data structures. various types of data structures are used for different types of applications; some are specialized in specific tasks.

Data structures are referred to as a collection of data values and relationships between them, functions, and operations applicable to the data. so that, users can easily access and modify the data efficiently.

Data structures help us to manage large amounts of data, such as huge databases. Efficient data structures are the fundamental basis for efficient algorithms. Besides efficient storage, data structures are also responsible for the efficient retrieval of data from stored locations. It includes an array, Graph, Searching, Programs, Linked List, Pointer, Stack, Queue, Structure, Sorting, and so forth.

The concepts of searching in a data structure, as well as its methods, are covered in this article.

What is Searching in Data Structure?

Searching in data structure refers to the process of finding the required information from a collection of items stored as elements in the computer memory. These sets of items are in different forms, such as an array, linked list, graph, or tree. Another way to define searching in the data structures is by locating the desired element of specific characteristics in a collection of items.

Different Searching Metods

Searching in the data structure can be done by applying searching algorithms to check for or extract an element from any form of stored data structure.

These algorithms are classified according to the type of search operation they perform, such as:

These methods are evaluated based on the time taken by an algorithm to search an element matching the search item in the data collections and are given by,

The best possible time

The average time

The worst-case time

The primary concerns are with worst-case times, which provide guaranteed predictions of the algorithm’s performance and are also easier to calculate than average times.

To illustrate concepts and examples in this article, we are assuming ‘n’ items in the data collection in any data format. To make analysis and algorithm comparison easier, dominant operations are used. A comparison is a dominant operation for searching in a data structure, denoted by O() and pronounced as “big-Oh” or “Oh.”

There are numerous searching algorithms in a data structure such as linear search, binary search, interpolation search, sublist search, exponential search, jump search, Fibonacci search, the ubiquitous binary search, recursive function for substring search, unbounded, binary search, and recursive program to search an element linearly in the given array. The article includes linear search, binary search, and interpolation search algorithms and their working principles.

Let’s take a closer look at the linear and binary searches in the data structure.

What is Linear Search?

The linear search algorithm iteratively searches all elements of the array. It has the best execution time of one and the worst execution time of n, where n is the total number of items in the search array.

It is the simplest search algorithm in data structure and checks each item in the set of elements until it matches the searched element till the end of data collection. When the given data is unsorted, a linear search algorithm is preferred over other search algorithms.

Complexities in linear search are given below:

Space Complexity

Since linear search uses no extra space, its space complexity is O(n), where n is the number of elements in an array.

Time Complexity

Best-case complexity = O(1) occurs when the searched item is present at the first element in the search array.

Worst-case complexity = O(n) occurs when the required element is at the tail of the array or not present at all.

Average- case complexity = average case occurs when the item to be searched is in somewhere middle of the Array.

Pseudocode for Linear Search Algorithm procedure linear_search (list, value) for each item in the list if match item == value return the item's location end if end for end procedure

Let’s take the following array of elements:

45, 78, 15, 67, 08, 29, 39, 40, 12, 99

To find ‘29’ in an array of 10 elements given above, as we know linear search algorithm will check each element sequentially till its pointer points to 29 in the memory space. It takes O(6) time to find 29 in an array. To find 15, in the above array, it takes O(3), whereas, for 39, it requires O(7) time.

What is Binary Search?

This algorithm locates specific items by comparing the middlemost items in the data collection. When a match is found, it returns the index of the item. When the middle item is greater than the search item, it looks for a central item of the left sub-array. If, on the other hand, the middle item is smaller than the search item, it explores for the middle item in the right sub-array. It keeps looking for an item until it finds it or the size of the sub-arrays reaches zero.

Binary search needs sorted order of items of the array. It works faster than a linear search algorithm. The binary search uses the divide and conquers principle.

Run-time complexity = O(log n)

Complexities in binary search are given below:

The worst-case complexity in binary search is O(n log n).

The average case complexity in binary search is O(n log n)

Best case complexity = O (1)

Pseudocode for Binary Search Algorithm Procedure binary_search A ← sorted array n ← size of array x ← value to be searched Set lowerBound = 1 Set upperBound = n while x not found if upperBound < lowerBound EXIT: x does not exists. set midPoint = lowerBound + ( upperBound - lowerBound ) / 2 if A[midPoint] x set upperBound = midPoint - 1 if A[midPoint] = x EXIT: x found at location midPoint end while end procedure

Example,

To find 61 in an array of the above elements,

The algorithm will divide an array into two arrays, 09, 12, 26, 39 and 45, 61, 67, 78

As 61 is greater than 39, it will start searching for elements on the right side of the array.

It will further divide the into two such as 45, 61 and 67, 78

As 61 is smaller than 67, it will start searching on the left of that sub-array.

That subarray is again divided into two as 45 and 61.

As 61 is the number matching to the search element, it will return the index number of that element in the array.

It will conclude that the search element 61 is located at the 6th position in an array.

Binary search reduces the time to half as the comparison count is reduced significantly as compared to the linear search algorithm.

What is Interpolation Search?

It’s a better version of the binary search algorithm that focuses on the probing position of the search element. It only works on sorted data collection, similar to binary search algorithms.

Complexities in interpolation search are given below:

When the middle (our approximation) is the desired key, Interpolation Search works best. As a result, the best case time complexity is O(1).

If the data set is sorted and distributed uniformly, the interpolation search’s average time complexity is O(log2(log2n)), where n denotes the total of elements in an array.

In the worst-case scenario, we’ll have to traverse the entire array, which will take O(n) time.

An interpolation search is used when the location of the target element is known in the data collection. If you want to find Rahul’s phone number in the phone book, instead of using a linear or binary search, you can directly probe to memory space storage where names begin with ‘R’.

Pseudocode for Interpolation Search Algorithm A → Array list N → Size of A X → Target Value Procedure Interpolation_Search() Set Lo → 0 Set Mid → -1 Set Hi → N-1 While X does not match if Lo equals to Hi OR A[Lo] equals to A[Hi] EXIT: Failure, Target not found end if Set Mid = Lo + ((Hi - Lo) / (A[Hi] - A[Lo])) * (X - A[Lo]) if A[Mid] = X EXIT: Success, Target found at Mid else if A[Mid] X Set Hi to Mid-1 end if end if End While End Procedure Master All Searching Techniques

Mastering the art of searching in data structures is essential in today’s data-driven world. By understanding and implementing efficient search algorithms, you can unlock valuable insights and make informed decisions. As you delve into data structures, consider taking your skills to the next level with our Blackbelt program. With its comprehensive curriculum and hands-on projects, the program equips you with the expertise to become a data structure expert. Join the Blackbelt program and embark on a transformative journey towards becoming a proficient data scientist capable of harnessing the power of data structures to drive meaningful impact and innovation.

Q1. What is searching and types?

A. Searching is the process of finding a particular piece of information or data from a larger set of data or information. There are various types of searching techniques, including linear search, binary search, hash search, and tree search. Linear search is a simple and straightforward method for finding data, while binary search is faster for larger sets of data. Hash search and tree search are specialized techniques for certain types of data structures.

Q2. What are the two types of searching in data structure?

A. The two main types of searching in data structure are sequential/linear search, where each element is checked sequentially, and binary search, which is faster and works by dividing the dataset in half and comparing the middle element with the target value until a match is found.

Q3. What is searching and sorting?

A. Searching and sorting are two fundamental operations in computer science and data structures. Searching refers to finding a specific element or value within a collection of data, while sorting involves arranging the data in a specific order, such as ascending or descending. These operations are used in many applications, such as information retrieval, database management, and computer algorithms. Efficient algorithms for searching and sorting are essential for optimizing the performance of many computer systems.

Related

## How To Open Windows Firewall Using Different Methods

Windows Defender Firewall is a software firewall component of the Windows operating system. In this post, we will show you how to open Windows Firewall using different methods on your PC to access the application whether you want to turn it off or just to make sure it is running to protect your PC.

How to open Windows Firewall in Windows 11/10

The Windows Defender Firewall can be configured to allow or block programs on a computer from accessing network or Internet resources. It also allows or blocks connections to and from other computers on a network. Essentially, as part of its built-in security suite for the Windows OS, the application works to protect the operating system and user data on the computer from improper or unapproved access, use, and possible infection. PC users can access or open the Windows Defender Firewall using any of the following methods below.

1] Control Panel

To open Windows Firewall in Windows 11 or Windows 10:

Press Windows key + R to invoke the Run dialog.

In the Run dialog box, type control and hit Enter to open Control Panel.

From the top right corner of the window, set the View by option to Small icons or Large icons.

Select Windows Defender Firewall.

2] Run Dialog

Press Windows + R key combination to open the Run dialog.

In the Run dialog box, type control chúng tôi hit Enter.

3] Windows Search

Type in windows defender firewall.

Read: How to Restore or Reset Windows Firewall settings to defaults

4] Windows Terminal

Press the Windows key + X to open Power User Menu.

Tap A on the keyboard to launch Windows Terminal in admin/elevated mode.

In the PowerShell console or CMD prompt, type control chúng tôi hit Enter.

Read: Best Free Firewall software for Windows

5] Desktop Shortcut

Read: Import, Export, Repair, Restore Default Firewall Policy in Windows

6] Keyboard Shortcut

To use a keyboard shortcut to open Windows Firewall, you will first have to create a desktop shortcut as shown above. Once you’ve done that, you can apply a hotkey to the Windows Firewall desktop shortcut by following these steps:

In the Shortcut key field, enter the Ctrl + Alt + F (where F is for firewall) hotkey.

Now, to open Windows Defender Firewall, simply press the Ctrl + Alt + F key combo. You can always change that hotkey by pressing a different key for it within the Shortcut key field. If you delete the Windows Defender Firewall desktop shortcut, the hotkey assigned to it will also be erased.

You can modify the registry to add a Windows Firewall option to the desktop context menu which contains the following actions:

Windows Firewall – opens the user interface of the app

Windows Firewall with Advanced Security – opens Windows Firewall with Advanced Security

Configure Allowed Apps

Turn On Windows Firewall

Turn Off Windows Firewall

Reset Windows Firewall

Windows Defender Security Center

Since this is a registry operation, it is recommended that you back up the registry or create a system restore point as necessary precautionary measures. To add Windows Firewall to Context Menu in Windows 11/10, do the following:

Press Windows key + R to invoke the Run dialog.

In the Run dialog box, type notepad and hit Enter to open Notepad.

Copy and paste the code below into the text editor.

Choose a location (preferably desktop) where you want to save the file.

Enter a name with a .reg extension (eg;  chúng tôi ).

Choose All Files from the Save as type drop-down list.

You can now delete the .reg file if you like.

If you want to remove the option, repeat the steps above but this time use the code below:

Windows Registry Editor Version 5.00 [-HKEY_CLASSES_ROOTDesktopBackgroundShellFirewallContextMenu]

That’s it on how to open Windows Firewall using different methods!

Read next: 10 ways to open Windows Security Center

Is there another way to get your firewall settings if so how?

You will find the native Firewall application for the Windows operating system in the System and Security section of the Control Panel app. Still, you can easily open or access the Windows Firewall’s settings by using any of the methods described in this post above. You can use the netsh command to verify the firewall settings. You will see information similar to the following: Profile = Domain. Exception mode = Enable. Multicast/broadcast response mode = Enable. Notification mode = Enable.

Read: Windows Defender Firewall is using settings that make device unsafe

How to open Windows Firewall ports command line?

Open the command prompt, type netstat –na, and hit Enter. Find port 445 under the Local Address from the output and check the State. If it says Listening, your port is open. To open the Remote Desktop port (port 3389) in Windows Firewall, go to Advanced settings on the left side and ensure that ‘Inbound Rules’ for Remote Desktop is ‘Enabled’.

## Calculating Investment Return In Excel

Calculating Investment Return In Excel

Investment return, or majorly called Return on Investment (in short ROI), is the basic mathematical and financial calculation we all have done in our earlier times in School. But we have not tried this on Excel. Calculating Investment Return In Excel is the way to determine how much or the percentage returns the investor will receive based on the amount being invested for the given time period. There are different ways to find the investment return or ROI depending on our input type. We will see that in below examples and descriptions below as well.

Excel functions, formula, charts, formatting creating excel dashboard & others

How to Use Calculating Investment Return In Excel?

Considering the mathematical expression of calculating Return on Investment (ROI), we have different ways to find it. But the most widely used method for finding ROI is shown below. Return on Investment can be the amount gained from the market, or it can be the percentage by which we have gained the investment using the below formulas;

ROI % = (Final Invested – Investment Amount)/ Investment Amount x 100

Where, Investment Amount – As clear the amount which is an investment into scheme or business

Final Invested – Amount being raised with a certain percentage

The first formula shown is the basic formula by which we can get the amount raised after the investment and another formula will give us the Return on Investment in percentage which is more precise.

Examples Example #1 – Calculating Investment Return In Excel

In this example, we will see one of the simplest ways to calculate Return on Investment of Investment Return. For this, we have considered simple sets of columns where we will calculate the ROI for the invested amount in 2023 to the investment return in 2023, 2023, and 2023 respectively, as shown below.

Let’s consider the invested amount in the year 2023 is Rs. 10,000/- for which we got the ending value or final return as shown below in respective year columns.

To calculate the ROI or Investment return of the investment in the year 2023, we need to follow the formula which we have seen above. Here are invested amount will be Rs. 10000/- and the final amount will be Rs. 11100/-. Now let’s put these values into the formula in cell C4 as shown below.

It is better to fix the cell of the Current Value because this will be seen in all the other ROIs of 2023-2023, as shown above. Now we will press enter to exit, this will show the ROI.

As we can see, the final percentage rate of return is coming at 11% for the year 2023.

Now we will drag this formula to other side cells to calculate the ROI of 2023 and 2023. The ROI of 2023 is coming 12%, and of 2023 is coming as -11%. This means that investor is getting profit in the year 2023 and 2023 & loss in the year 2023.

Example #2 – Calculating Investment Return In Excel

In this example, we will see how to calculate ROI and Annualized return using the formula below. To calculate the annualized return, we will be using the below formula.

R= ((Invest Amount + Gain)/Invest Amount)^(365/Days)-1

We have a different set of data as shown below.

We can then calculate the days invested using the TODAY function with Investment date there in cell A2.

Now using the formula we have seen in example-1, as shown below. This would get us the %age ROI.

Calculate the Annualized Return, our final ROI for the year (considering 365 Days). Using the formula, first, add Invested amount and Gain and divide the sum with an Invested amount to calculate the whole ROI. And then, give it the power of 365 days of the year, divide it using the days invested, and subtract it by 1 to get the final value.

Pros of Calculating Investment Return In Excel

All the methods shown in the above examples are very easy to implement.

Better calculating ROI is by using the mathematical formula we have seen in the above examples and then implementing them in Excel.

Things to Remember About Calculating Investment Return In Excel

ROI and ROI Percentage are both different. ROI is the value or amount gained or lost in the time interval, and ROI Percentage shows the exact reference value by which change is observed.

Annualized Return helps us to calculate the ROI through the year or better we say at the end of the year.

Fixing the cells when moving and pasting the formula into different cells is better. It helps in keeping the right formula value in the cell.

We also have different methods in statistics that are not bound to the methods we have seen in the above examples.

Recommended Articles

This is a guide to Calculating Investment Return In Excel. Here we discuss the definition, How to Use Calculating Investment Return In Excel? Excel template. You may also have a look at the following articles to learn more –

## Ensemble Methods In Machine Learning

Introduction to Ensemble Methods in Machine Learning

Hadoop, Data Science, Statistics & others

Types of Ensemble Methods in Machine Learning

Ensemble Methods help to create multiple models and then combine them to produce improved results, some ensemble methods are categorized into the following groups:

1. Sequential Methods

In this kind of Ensemble method, there are sequentially generated base learners in which data dependency resides. Every other data in the base learner is having some dependency on previous data. So, the previous mislabeled data are tuned based on its weight to get the performance of the overall system improved.

Example: Boosting

2. Parallel Method

In this kind of Ensemble method,  the base learner is generated in parallel order in which data dependency is not there. Every data in the base learner is generated independently.

Example: Stacking

3. Homogeneous Ensemble

Such an ensemble method is a combination of the same types of classifiers. But the dataset is different for each classifier. This will make the combined model work more precisely after the aggregation of results from each model. This type of ensemble method works with a large number of datasets. In the homogeneous method, the feature selection method is the same for different training data. It is computationally expensive.

4. Heterogeneous Ensemble

Such an ensemble method is the combination of different types of classifiers or machine learning models in which each classifier built upon the same data. Such a method works for small datasets. In heterogeneous, the feature selection method is different for the same training data. The overall result of this ensemble method is carried out by averaging all the results of each combined model.

Example: Stacking

Technical Classification of Ensemble Methods

Below are the technical classification of Ensemble Methods:

1. Bagging 2. Boosting

The boosting ensemble also combines different same type of classifier. Boosting is one of the sequential ensemble methods in which each model or classifier run based on features that will utilize by the next model. In this way, the boosting method makes out a stronger learner model from weak learner models by averaging their weights. In other words, a stronger trained model depends on the multiple weak trained models. A weak learner or a wear trained model is one that is very less correlated with true classification. But the next weak learner is slightly more correlated with true classification. The combination of such different weak learners gives a strong learner which is well-correlated with the true classification.

3. Stacking

This method also combines multiple classifications or regression techniques using a meta-classifier or meta-model. The lower levels models are trained with the complete training dataset and then the combined model is trained with the outcomes of lower-level models. Unlike boosting, each lower-level model is undergone into parallel training. The prediction from the lower level models is used as input for the next model as the training dataset and form a stack in which the top layer of the model is more trained than the bottom layer of the model. The top layer model has good prediction accuracy and they built based on lower-level models. The stack goes on increasing until the best prediction is carried out with a minimum error. The prediction of the combined model or meta-model is based on the prediction of the different weak models or lower layer models. It focuses to produce less bias model.

4. Random Forest

The random forest is slightly different from bagging as it uses deep trees that are fitted on bootstrap samples. The output of each tress is combined to reduce variance. While growing each tree, rather than generating a bootstrap sample based on observation in the dataset, we also sample the dataset based on features and use only a random subset of such a sample to build the tree. In other words, sampling of the dataset is done based on features that reduce the correlation of different outputs. The random forest is good for deciding for missing data. Random forest means random selection of a subset of a sample which reduces the chances of getting related prediction values. Each tree has a different structure. Random forest results in an increase in the bias of the forest slightly, but due to the averaging all the less related prediction from different trees the resultant variance decreases and give overall better performance.

Conclusion

The multi-model approach of ensemble is realized by deep learning models in which complex data have studied and processed through such different combinations of the classifier to get better prediction or classification. The prediction of each model in ensemble learning must be more uncorrelated. This will keep the bias and variance of the model as low as possible. The model will be more efficient and predict the output under minimum error. The ensemble is a supervised learning algorithm as the model is trained previously with the set of data to make the prediction. In ensemble learning, the number of component classifiers should be the same as class labels to achieve high accuracy.

Recommended Articles

This is a guide to Ensemble Methods in Machine Learning. Here we discuss the Important Types of Ensemble Methods in Machine Learning along with Technical classification. You can also go through our other suggested articles to learn more –

## Various Methods To Use Macros In Excel

Introduction to Excel Macro

There are some tasks in Excel that we do daily. It could be our routine tasks that may include writing specific formulas, copying and pasting data, updating some columns, or writing a text several times at different locations in a sheet. To avoid this repetition of tasks and save time, we can use Macros in Excel. Macros are the tool that automates tasks, saves time, and reduces errors. A Macro program copies keystrokes or mouse actions repeated and common in the sheet. In this article, we will learn about Examples of Excel Macro.

Excel functions, formula, charts, formatting creating excel dashboard & others

Methods to Use Macros in Excel

Below we will discuss the different methods of using Macros in Excel.

You can download this Examples of Excel Macros Template here – Examples of Excel Macros Template

Method 1 – Enabling Macros in Excel

To add the Developer tab in the ribbon, open an Excel workbook from the File menu.

Select the Developer (Custom) option and then press OK.

You will see the Developer tab on the ribbon.

Method 2 – Creating Macros using Record Button

We can record a macro and use it any number of times. We can record a task that we use daily or which is repetitive. Once the Macro is recorded, we can run it, and our work will be done.

Let’s take the example of recording a Macro.

Example:

I have recorded this table.

If you want to paste the data in sheet 2, just go to sheet 2 and press Ctrl+V or view macros.

Method 3 – How to Save a File with Macro?

We can only save the files with macros as Excel Macro-Enabled Template (file type).

It can’t be saved as a normal Excel file.

Method 4 – Writing Macros in VBA

Examples of Excel Macro

Below we will discuss the examples of Excel Macro

Example #1 – Write a Program to get Text Hello

Now write a short program in VBA. We will write a program to get the “Hello “your name” text in the message box every time we enter a name in an input box.

We will write our first program in Module 1. We always start our program with “Sub” and end with End Sub in VBA.

Sub hello_word () Dim name As String name= InputBox ("Input your name") MsgBox "Hello" + name End Sub

So now we write the program as below:

Explanation:

“Dim name as a string” defines the name as a string.

Then an inbuilt Inputbox function will ask for a name to be filled in, which will be stored in a variable name.

Msgbox+name will display the name in the msg box. We will then create a command button by going to the Developer tab, then the Inset tab. Then form control, command button.

I entered my name, and it displayed.

Every time you press button 1, you will see the input box, just enter the name and see the “hello+name.”

Example #2 – Writing a Shortcode using For Inside a Loop

In this example, we will work with For. Suppose we want to fill cells A1 to A10 with 1,2,3…. to 10. We can do this by writing a shortcode using For Inside a Loop.

Example #3 – Display Total chúng tôi Odd and Even Numbers

Using If, we can write a logical macro. We will write a code to create a table, and at last, there will be msg box displaying the total no. of odd and the total no. of even numbers. So we will write a code:

We will start the program with Sub odd even (name).

We will then take x as an input box to input a number. Then in variable a, we will store a loop from 1 to 10.

In cell A1 of sheet 3, we will make a table by multiplying it with x in a loop until A10.

We will then condition that if cell A1 is even, it will be added to a variable y that is currently 0, and odd will be added to a variable z, which is also 0. Then we will end the if statement and write the next A for the loop to work.

In the end, we will add a msgbox to display the sum of odds and its total number and the sum of even and its total number. End the program.

Run the program with F5. I entered 6 in the input box.

Example #4 – Write a Program to Pop as per the Defined Age

In this example, we will use the sentence case to give results as per some specific conditions. In this example, a message will pop up as per a defined age group of people once you select a cell mentioning age.

We will write the program as below:

The program will start as Sub Agegroup.

Cell value will be defined as an integer, which will be the active cell value, meaning that one is selected.

We will then use a select case in different cases per the age group. The message box will then appear according to age.

If the age selected is not found in the select cases, it will give the message an unknown age.

End Select End program

Some numbers are mentioned, and once I select 12 and run the code. It will show the result below:

It will give the age group, as shown below.

These were some examples of VBA macros.

Things to Remember

Always save the Excel file containing macros as an Excel Macro enabled template as the file type. Else the macros will not be saved.

The program name should not contain any spaces.

It is good to write programs in modules as it is not sheet specific.

Be careful while recording macros because if you make a mistake or repeat a step, it records them as the same and does not provide the correct output.

Recommended Articles

This is a guide to Examples of Excel Macro. Here we discuss the Introduction to Excel Macro and the various Methods to Use Macros in Excel. You can also go through our other suggested articles –

Update the detailed information about Calculating Correlation In Different Methods on the Achiashop.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!