Learn Microsoft Excel Online FreeSuppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative numbers ignored. In this article, we will help you to construct a formula with AVERAGE… read more »
If both positive numbers and negative numbers exist in the same column, when sorting them by absolute values, we can sort them with the help of ABS function and helper column. In this article, we will show you the way to sort numbers by absolute values. EXAMPLE We want to sort numbers in “Numbers” column… read more »
VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today, in this article, we will show you the way to apply VLOOKUP to retrieve employee information. I hope this article will help you in your daily work. EXAMPLE The left table contains employee information like HRID, name, age, and… read more »
VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today we will show you the application of VLOOKUP function when there are two lookup tables. EXAMPLE Table1 and table2 record the rates of Y2020 and Y2021 separately. Rates are increased in Y2021. In “Statistic” table, we want to fill… read more »
VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match by setting lookup matching modes. Today, we will show you the way to use VLOOKUP… read more »
VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match by setting lookup matching modes. Today, we will show you the way to apply VLOOKUP… read more »
VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match by setting lookup matching modes. Today, we will show you the way to apply VLOOKUP… read more »
VLOOKUP is one of the key functions among all lookup & reference functions in Excel. It can scan and retrieve data from a static or dynamic table based on your lookup value. It can perform approximate match or exact match by setting lookup matching modes. Today, we will show you the way to apply VLOOKUP… read more »
Today, we will show you how to use SUMPRODUCT and EXACT to perform a case sensitive exact match. In this article, we provide a simple example to calculate bonus for employees whose names are case-sensitive. If you meet similar scenarios in your daily work, you can directly use this formula to deal with your problem…. read more »
In Excel, INDEX function and MATCH function are often used together for retrieving data from a particular position. MATCH function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting different match types. They are not case-sensitive functions. If we need to perform a case-sensitive lookup, we… read more »
Microsoft Excel provides many functions that can execute logical test, search data, return current date and something else. They are very useful in daily work. And Excel VLOOKUP function is one of Excel most frequently used functions. It belongs to lookup & reference functions and it can perform approximate match or exact match by setting… read more »
In Excel, except combination INDEX+MATCH, we can also apply other functions to search data, for example VLOOKUP function. Like MATCH function, VLOOKUP function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting lookup matching modes. Today, we will show you the way to calculate “Grade” by… read more »
In Excel, except combination INDEX+MATCH, we can also apply other functions to search data, for example VLOOKUP function. Like MATCH function, VLOOKUP function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting lookup matching modes. Today, we will show you the way to calculate discount by… read more »
In Excel, INDEX function and MATCH function are often used together for returning value or cell reference or range reference from specified position. And MATCH function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting different match type values. Today, we will show you how to… read more »
In Excel, INDEX function and MATCH function are often used together for returning value or cell reference or range reference from specified position. And MATCH function is one of Excel lookup & reference functions that can perform approximate match by setting match type. Today, we will show you the basic usage of INDEX and MATCH… read more »
Suppose you are going to count the average working hours of employees in the company based on different months. If a worksheet including working hours on different dates and months is prepared for you, do you know how can we calculate the average of working hours based on months? In this article, through explaining the… read more »
We usually apply AVERAGE function or relevant functions to return average directly in Excel worksheet. But in some situations, only applying average relevant functions cannot figure out our problem. Sometimes we can create a formula with functions and mathematical operation inside to calculate average depends on different situations. In this article, through explaining the example… read more »
Suppose you are counting the numbers of participants who are the winners in a competition. And the passing line is provided as pass 4 out of 5 rounds of games. You need to know if participant pass the game or not firstly. If you are familiar with the functions in Excel and can use them… read more »
AVERAGEIFS function is one of the most popular functions in Excel. Apply AVERAGEIFS function, we can calculate average simply if there are multiple conditions. In this article, we will introduce you to apply AVERAGEIFS function to calculate average of a set of numbers which are filtered by multiple criteria. You can get the basic knowledge… read more »
AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average properly based on different situations. In this article, we will introduce you the way to calculate average of the top N numbers from a range. To approach our goal, except the main AVERAGE… read more »
