Tag: Excel Applications

Using VBA logical operators AND - OR

VBA Logical Operators in Microsoft Excel

 

 

 

The most common logical operators in VBA are: AND and OR. Each of these operators is very useful for evaluating conditions and making appropriate decisions about the code that will be executed

 

The AND Logical Operator

 

The AND logical operator is the operator that helps us to enforce two conditions. This operator allows to execute a block of code that must meet condition1 AND condition2.

In the following example I have the qualification of two exams. Only if both exams have a grade higher than 3, then the student will be approved, otherwise the grade will be disapproval.

 

VBA Logical Operators Initial Example

 

We are going to use an IF statement to compare the exams qualification with the grade condition. This comparison is that we usually do with the logical operators.

The code that will be executed when the button is pressed will be the following:

 

VBA Logical Operators AND Code

 

When we execute this code we will obtain the “Approved” result, this is because both exams have a rating greater than 3:

 

VBA Logical Operator AND approved example

 

In this way we verify that the logical operator And helps us to force both conditions to be fulfilled. However, if the value of one of the cells is less than 3, then we will have a different result:

 

AND Logical Operator Reprobate Example

 

The logical operator And will return the true value only when both conditions are met and it will suffice that one of them is not met to obtain a negative result.

 

The OR Logical Operator

 

The OR logical operator translates will allow us to know if at least one of the conditions is fulfilled, that is to say, if the condition1 OR condition2 is fulfilled.

 

If we change the previous example a bit and say that it is sufficient that one of the two grades is greater than 3 for the student to be approved, then we can modify the code as follows:

 

Code for OR logical operator example

 

If any of the grades are higher than 3, then the student will be approved:

 

OR logical Operator Approved Example

 

The only way the logical OR operator returns a false value is that none of the conditions are met. In our example, the student will be reprobate only when both grades are less than 3:

 

OR Logical Operator Reprobate Example

 

To conclude in the following table we can see in each case how the logical operators evaluate the condition:

 

Conditions Table for VBA Logical Operators AND - OR

 

Excel Total 

 

 

Excel Visual Basic (VBA) for Beginners - Use Excel VBA to Cleanse a Dataset

How to Select Cells by Value in Microsoft Excel

 

 

When you have a lot of data in a table and want only to select a group of cells with a certain value we can use Excel search for quick selection of those cells.

From the following table of data I want to select all the cells that have the value $ 1,000 and once selected place the cell text in bold

Select Cells by Value

Selecting by the Search Method

An alternative is to use the Excel search box to select the cells that meet the condition set. To do that we open the search bog, then we specify the value we’re looking for and finally click the Find All button.

Find All Selected Data by Value

Excel display all the cells that match the specified value, we select only the results for the worksheet cells are also selected Tip: To select all results can click on the first row of results and then press the Shift key and click the last row. Since you have selected all results, close the search window and observe the cells selected in the worksheet ready to apply the text in bold

Formatting Select Data

Using a Conditional Formatting Rule

If we make a selection of cells to make a format modification, then we can use an alternative method Once we have selected the range containing the information (b2:E16) I must press the Condittional Format button on the Home tab and select the Highlight cell rules option and then Equal To button.

Conditional Formatting to Select Cells by Value

In the dialog box I have to specify the quantity that I’m looking for and select the Custom Format option.

Custom Format

Then I’m going to select a Bold style for the fonts to format the cells that comply with this rule

Conditional Formatting Bold Style

Then click OK to close the dialog box and then click OK to apply the conditional formatting rule, we will have the cells of value equal to $1000 with a bold style format. Excel Total
 

 

How to Use Excel Comments

How to Use Excel Comments

 

 
 

Excel allows you to insert comments in any cell of your Worksheet in Microsoft Excel. Those Excel Comments are very useful to make a check, give some instructions or simply give a detailed explanation about the cell content.

Add a comment to a Cell

The first step is to select the cell that you will add the comment and then go to the Review tab and in the group Comments and press the New Comment button (In MAC only New). Clicking on that button, the box necessary to enter the comment is displayed.

Add a Microsoft Excel Comment

Enter your comment in the box and to finish click outside the box. If you need to enlarge the box only positions the mouse over any of the corners and drag the edge to have the desired size. Excel identifies a cell that has a comment through a small triangle in the upper right corner of the cell. To see the comment from a cell only you must position the mouse over that cell and Excel will display the comment.

Modify or Eliminate a Comment

When you select a cell containing a comment Edit and Delete Comment buttons are enabled.

Excel Comment Options

Clicking on the Edit Comment button will show the comment box allowing you to do the editing again. If instead you want to delete the comment is sufficient to press the Delete button. Now if you have an Excel worksheet that contains many comments, you can use the Previous and Next buttons to move between each. Or conversely, if you want to display all comments sheet at the same time you can click Show All Comments button. You can learn more about Microsoft Excel Comments and Review options here: Excel School
 

 

Excel Visual Basic (VBA) for Beginners - Use Excel VBA to Cleanse a Dataset

Excel Visual Basic (VBA) for Beginners to Cleanse a Dataset

 

 
This Excel video series helps Excel users just starting out with code in the Visual Basic program, or those who have never coded before, who are curious about it and just heard about it, about its powerful utilisation. In a lot of cases, Excel users find themselves engaged in tricky manual tasks in study subjets or work tasks, that take up time, create stress and lose of time. Data cleansing is one such task.

To explain that, if you have ever gone through a list of data and looked for mistakes (in dates or numbers, for example) then you have done a data cleansing task. With a little coding knowledge, that we are going to see in the following videos, it is possible to dramatically speed up this kind of task, reduce the stress and increase your productivity. You can learn more about Microsoft Excel go on here: Excel School

The 1st Part: Introduction

The 2nd Part: Beginning with VBA

The 3rd Part: Using Messaging Boxes

The 4th Part: Stopping the program

The 5th Part: Finishing and testing the program

 

 

PMT Function Calculating a loan payment

PMT Function Calculating a Loan Payment

 

 

In this video, learn how to calculate the payment for a loan based on constant payments and a constant interest rate. You need to know: the interest rate, the number of periods and the present value of the loan. You can learn more about Microsoft Excel financial functions here: Excel School

 

 

Dependent Data Validation Lists in Microsoft Excel

Dependent Data Validation Lists in Microsoft Excel

This video will walk you through the process of setting up and using dependent data validation lists with Microsoft Excel. You could refer to this as cascading data validation. This is very useful to create templates of databases that you need to be completed by other users. The other users only have a limited option to fill each cell. You can learn more about Microsoft Excel productivity here: Excel School

 

Using Navigation Tools in Microsoft Excel

Using Navigation Tools in Microsoft Excel

Learn about essential training in Microsoft Excel, one of the first tools that you have to begin to use, the navigation. Save a lot of time, learning basic keyboard combinations, or commands with the mouse, to navigate between worksheets, workbooks, cells, columns, rows, etc. And begin to increase of the productivity in the using of Microsoft Excel You can learn more about Microsoft Excel productivity here: Excel School

List of Reminders and Future Tasks in Microsoft Excel

In Microsoft Excel, you can manage all your tasks, or to-do lists in an easy way. With the following video you can see how easy you can manage your time. You now can see that Excel is a place where you can make a lot of things, including some simple applications that allows you to save time.

 

PV function and FV function in Microsoft Excel

Present Value and Future Value in Microsoft Excel

One of the most commonly used financial functions in Excel are Present value (PV) and Future Value (FV). Both functions use similar arguments so it is only necessary to understand the terminology to use them properly.

The PV() and FV() functions in Excel The arguments for those functions are:

– Nper: The total number of periods. For example, if a monthly payment is made for one year periods total will be 12. If a payment is made monthly for 3 years we will have a total of 36 periods.

– PMT: The payment made each period.

– Rate: The constant interest rate for each period. When we use financial functions must remember that the values can be positive or negative depending on whether you are receiving money or if you are paying. Another important thing to note is that generally interest rates are expressed on an annual basis, so if you’re doing a monthly calculation we divide the interest rate by 12.

The basic sintax for both functions is the following: PV (Rate, Nper, PMT) FV (Rate, Nper, PMT) Now, see the following calculations:

The FV function and the PV function in Microsoft Excel

In this example I have used the same parameters for both the VA function and for the VF function. The future value helps us know how much money we will have in a future date if you start investing today the amount specified with a fixed interest rate. The present value is the value today that we will be making an investment in future payments at a fixed interest rate. Excel Total