Advanced Excel – Lookup, If, and Time Functions
Many functions in Excel do not simply perform calculations. One example is the vlookup function. Other functions, such as if functions perform calculations based on whether certain criteria are met, and other functions display or calculate based on dates and times. In this section, At Advanced Excel – Lookup, If, and Time Functions section we will look at these functions and how to use them in a sheet.
Also You can get known by review our previous lessons:
Advanced excel first lecture
Advanced Excel – Advanced Formatting
Advanced Excel – Advanced Formulas and Features
- With Students_Grades.xlsx open, select E2.
In this column, we want to display the respective letter grades based on the grade range found in the letter grade table.
- Type “=vlookup(“
This is the function that will look up a value an decide where it is on our table. The difference between the vlookup and the hlookup functions is the orientation of the table. In our sheet, the table is vertical, so we use vlookup, but if the table was horizontal, we would use hlookup.
- Select column D and type a comma.
The first entry for this formula designates what will be evaluated. We want to return a letter grade based on the percent found in cell D2.
- Type “Letter_Grade_Table,”.
This tells the function where to get the return data. Here, if data is greater than a value in the left column on the table, it returns the value from that row.
- Type “2)”.
This tells Excel that the value returned will be from the second column in the table. If the table was horizontal, it would signify the second row.
- On the keyboard press enter.
In our case, the data is in ranges; however, if our data was meant to matchup exactly to the data in the table, we could specify that here.
- Copy the formula down the rest of the column.
- Click into K7.
This is the cell where we want to display how many of each letter there is.
- Type “=countif(“
This formula has two arguments, a range and criteria. The range is simply where the data we are counting is located. The criteria designate what will be counted.
- Choose column E and type a comma.
All of the letters that will be counted is in this column.
- Choose cell J7 and press ctrl + enter.
- Copy the formula through the rest of the table.
- Open Employee_Info.xlsx and choose the sheet labeled Status.
On this sheet, there is a list of employees along with some pay info. There is also a table that is meant to display totals based on the employee’s status. We want to calculate the total salaries for each type of employee and place them in this table. The sumif function will help us do that.
- Type “=sumif(“ into J4.
This function has three arguments. The first is the range where the if statement will look. The criteria is what the if statement is looking for in the first range, and the sum range is what will be added if the criteria is met.
- Select Column F for the first entry and type a comma.
Since we are summing the salaries of Full Time employees, we want the function to look in this column for that information.
- Type “ I4,”.
If this information was not in the sheet, typing “Full Time” including the parenthesis would suffice.
- Last choose column D for the sum range.
- Copy this formula into cell J5.
Now that we know how to use the sumif function, we can fill out the rest of the table.
- Repeat these steps for the rest of this table using column E for the sum range.
- In cell G2, type
- Copy the function through the column.
- Navigate to the Time Worked worksheet.
In this sheet there are is a list of employees along with their starting and projected ending dates. We will be calculating both how many months have been worked and how many months are projected to be worked per person.
- In cell E2, type “=datedif(“.
This is a hidden function in Excel. If you search for it, you will not be able to find it, but it is still a useable function. There are three arguments in the formula which are: The first date, the date that the first date is being subtracted from, and how the result will be displayed. The possible choices for display are year (y), month (m), day (d), days since last full year (yd), months since last full year (ym), and days since last full month (md).
- Select cell C2 and D2 typing a comma after each.
This is telling excel where to find the dates used in the difference.
- Type “m” including the quotations and press enter.
When the function is run, it displays the months. This function does not round, so if someone worked 29 days, the function would say that they worked 0 months.
- Fill in the rest of the table with this function.
- Select F2.
We know want to display how many months have currently been worked. Since we don’t want to update this function every time we open the file, we will use a function that always relies on the current date.
- Type “=datedif(C2,today(), “m”)” and press enter on the keyboard.
The today function returns the current date, so in this formula, Excel is subtracting the date in C2 from today’s date and displaying the number in months. This screenshot is from December 20th, 2012, so it makes sense that F2 is displaying a four with this formula.
- Copy this formula through the table.
We have just looked through some advanced features and formulas that are useable in Excel. With some of these formulas, specific types of data must be used. The next section covers how to ensure that data is entered in a specific way.