The VLOOKUP function only looks to the right. To look up a value in any column and return the corresponding value to the left, simply use the INDEX and the MATCH function. 1. The MATCH function returns the position of a value in a given range. Explanation: 104 found at position 4 in the range $G$4:$G$7. 2. Use this result and the INDEX function to …
Locate Maximum Value in Excel This example teaches you how to find the cell address of the maximum value in a column. 1. First, we use the MAX function to find the maximum value in column A. 2. Second, we use the MATCH function to find the row number of the maximum value. Explanation: the MATCH function reduces to …
Applying Excel INDIRECT function to Cell Reference, Range Reference, Named Range, Worksheet Reference. Use the INDIRECT function in Excel to convert a text string into a valid reference. You can use the & operator to create text strings. Cell Reference Use the INDIRECT function in Excel to convert a text string into a valid cell reference. 1. For example, take a look …
This example teaches you how to perform a two-column lookup in Excel. See the example below. We want to look up the salary of James Clark, not James Smith, not James Anderson. 1. To join strings, use the & operator. 2. The MATCH function returns the position of a value in a given range. Insert the MATCH function …
To find the closest match to a target value in a data column, use the INDEX, MATCH, ABS and the MIN function in Excel. Use the VLOOKUP function in Excel to find an approximate match. 1. The ABS function in Excel returns the absolute value of a number. Explanation: C3-F2 equals -39. The ABS function removes the minus sign (-) from a …
A clever Excel trick to change negative numbers to zero but leave positive numbers unchanged. Below you can find two columns with numbers. To subtract the numbers in column B from the numbers in column A, execute the following steps. 1. Select cell C1. 2. Enter the formula =A1-B1 3. Click the lower right corner of cell C1 and …
By default, text is left-aligned and numbers are right-aligned in Excel. Atimes user enter inverted comma before inputting data values in a cell which automatically converts a number to text. This example teaches you how to convert ‘text strings that represent numbers’ to numbers. 1. Select the range A1:A4 and change the number format to General. 2. Numbers preceded by …
The ABS function in Excel returns the absolute value of a number. In other words: the ABS function removes the minus sign (-) from a negative number, making it positive. 1. For example, the ABS function in cell B1 below returns the absolute value of a negative number. The ABS function has no effect on 0 (zero) or positive …
The FORECAST and TREND function give the exact same result. When you add a trendline to an Excel chart, Excel can display the equation in a chart (see below). You can use this equation to calculate future sales. Explanation: Excel uses the method of least squares to find a line that best fits the points. The R-squared value equals 0.9295, which is a …
This example teaches you how to create a box and whisker plot in Excel. A box and whisker chart shows distribution of data into quartiles, highlighting the mean and outliers. The boxes may have lines extending vertically called “whiskers”. These lines indicate variability outside the upper and lower quartiles, and any point outside those lines or whiskers is …
This example teaches you how to use the PERCENTILE and QUARTILE function in Excel. Below you can find a list of scores (green fill for illustration only). 1. Use the PERCENTILE function shown below to calculate the 30th percentile. Excel returns the value 12.7. This means that 30% (6 out of 20) of the scores are lower or …
To calculate a weighted average in Excel, simply use the SUMPRODUCT and the SUM function. 1. The AVERAGE function below calculates the normal average of three scores. Suppose your teacher says, “The test counts twice as much as the quiz and the final exam counts three times as much as the quiz”. 2. Below you can find the …
This example illustrates two functions to chop off decimals in Excel. The INT and the TRUNC function. Int The INT (Integer) function rounds a number down to the nearest integer. Conclusion: the INT function only chops off decimals from positive numbers. Trunc Truncate a number to a given precision. The TRUNC (Truncate) function always chops off decimals. The TRUNC function can keep …
Find Nearest Multiple in Excel This example illustrates three functions to round numbers to a multiple of x in Excel. The MROUND, CEILING and the FLOOR function. Mround 1. For example, round a number to the nearest multiple of 10. 2. For example, round a number to the nearest multiple of 5. Ceiling The same as MROUND but rounds up. Floor The same as …
This function combines the MAX and IF function in order to get the maximum value in a data set of Excel worksheet. To look up the maximum value in a range based on specific criteria, you can use a basic array formula based on the IF function. {=MAX(IF(criteria_range=criteria,value_range))} Use the MAXIFS and MINIFS function in Excel …
This example illustrates the EVEN and the ODD function in Excel. It also shows you how to determine if a number is even or odd. Even 1. Rounds a positive number up to the nearest even integer. 2. Rounds a negative number down to the nearest even integer. Odd 1. Rounds a positive number up to the nearest odd integer. …
Excel functions such as SUM, COUNT, LARGE and MAX don’t work if a range includes errors. However, you can easily use the AGGREGATE function to fix this. 1. For example, Excel returns an error if you use the SUM function to sum a range with errors. 2. Use the AGGREGATE function to sum a range with errors. Explanation: …
This example illustrates the IFERROR function and how to return blank in place of #DIV/0! error in Excel. 1. For example, Excel displays the #DIV/0! error when a formula tries to divide a number by 0. 2. Use the IFERROR function. If a cell contains an error, an empty string (“”) is displayed.
IF function and ISERROR function are used to check for an error in Excel. This example shows you how to create an array formula that counts the number of errors in a range. 1. We use the IF function and the ISERROR function to check for an error in the record below: Explanation: the IF function returns 1, if an …
How to count data based upon multiple criteria? Counting with Or criteria in Excel can be tricky. This article shows several easy to follow examples. 1. We start simple. For example, we want to count the number of cells that contain Google or Facebook (one column). 2a. However, if we want to count the number of rows that contain Google or Stanford …
This example shows you how to create an array formula that sums every nth row in Excel. We will show it for n = 3, but you can do this for any number. 1. The ROW function returns the row number of a cell. 2. The MOD function gives the remainder of a division. For …
LARGE function example shows you how to create an array formula that sums the largest numbers in a range. Syntax: LARGE(array, k) Returns the k-th largest value in a data set. You can use LARGE to return the highest, runner-up, or third-place score. For example, 1. Find the second to largest number, use the following function. 2. To sum …
This example shows you how to create an array formula that sums a range with errors. You can also use the AGGREGATE function to sum a range with errors. 1. We use the IFERROR function to check for an error. Explanation: the IFERROR function returns 0, if an error is found. If not, it returns the value of the cell. …
By default, numbers are right-aligned and text is left-aligned in Excel. This example teaches you how to convert numbers to ‘text strings that represent numbers’. 1. Select the range A1:A4 and change the number format to Text. 2. Precede a number by an apostrophe and it will also be treated as text. 3a. If you add text to a number and …
The powerful SUMIF function in Excel sums cells based on one criteria. This page contains many easy to follow SUMIF examples. Numbers 1. The SUMIF function below (two arguments) sums values in the range A1:A5 that are less than or equal to 10. 2. The following SUMIF function gives the exact same result. It uses the & operator to …
This example illustrates how to concatenate (join) strings in Excel. When joining text and a number, use the TEXT function to format that number. 1. Simply use the & operator to join strings. Note: to insert a space, use ” ” 2. The CONCATENATE function produces the exact same result. 3a. The CONCAT function in Excel 2016 produces the exact …
In Excel, <> means not equal to. The <> operator in Excel checks if two values are not equal to each other. Let’s take a look at a few examples. 1. The formula in cell C1 below returns TRUE because the text value in cell A1 is not equal to the text value in cell B1. 2. …
This example shows you how to create an array formula that counts unique values. Examples: 1.. To count the number of 5’s, use the following function. We use the COUNTIF function. 2. To count the unique values (don’t be overwhelmed), we add the SUM function, 1/, and replace 5 with A1:A6. 3. Finish by pressing CTRL + SHIFT …
What is Standard Deviation (STDEV.P, STDEV.S)? This page explains how to calculate the standard deviation based on the entire population using the STDEV.P function in Excel and how to estimate the standard deviation based on a sample using the STDEV.S function in Excel. What is Standard Deviation? Standard deviation is a number that tells you how far numbers are …
COUNTIF is one of the statistical functions, it is used to count the number of cells that meet a criterion This chapter covers many easy to follow COUNTIF examples like Numbers, Text, Booleans, Errors and ‘Or Criteria’. Numbers 1. The COUNTIF function below counts the number of cells that contain the value 20. 2. The following COUNTIF …
Excel has two useful functions when it comes to generating random numbers. The RAND and RANDBETWEEN function. Rand The RAND function generates a random decimal number between 0 and 1. 1. Select cell A1. 2. Type RAND() and press Enter. The RAND function has no arguments. 3. To generate a list of random numbers, select cell A1, click on …
The VLOOKUP function is one of the most popular functions in Excel. This chapter contains many easy to follow VLOOKUP examples for Exact Match, Approximate Match, Right Lookup, First Match, Case-insensitive, Multiple Criteria, #N/A error and Multiple Lookup Tables. Exact Match Most of the time you are looking for an exact match when you use the VLOOKUP function in Excel. …
IF function is one of the most used functions in Excel. This page contains many easy to follow IF examples. Simple If Examples The IF function checks whether a condition is met, and returns one value if true and another value if false. 1a. For example, take a look at the IF function in cell B2 below. …
The OFFSET function in Excel returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells. OFFSET function Syntax; OFFSET(reference, rows, cols, [height], [width]) 1. The OFFSET function below returns the cell that is 3 rows below and 2 columns to the right of cell A2. The …
This chapter illustrates three Mathematical functions ROUND, ROUNDUP and ROUNDDOWN. Navigation: Formula Tab → Function Library Group → Math & Trig Category Before your start: if you round a number, you lose precision. If you don’t want this, show fewer decimal places without changing the number itself. Round Syntax: ROUND(number, num_digits) 1. Round a number to two decimal places. …
In addition to formulas, another way to conduct mathematical computations in Excel is through functions. Statistical functions apply a mathematical process to a group of cells in a worksheet. This chapter gives an overview of some very useful statistical functions like: AVERAGE function, AVERAGEIF function, MEDIAN function, SUMIF function, MIN function, MODE function, STEDV function, LARGE function, SMALL function. Average To calculate the average of a …
Learn all about Excel’s PMT, RATE, NPER, PV and FV To illustrate Excel’s most popular financial functions, we consider a loan with monthly payments, an annual interest rate of 6%, a 20-year duration, a present value of $150,000 (amount borrowed) and a future value of 0 (that’s what you hope to achieve when you pay off a loan). We make monthly payments, …
Learn all about Excel’s lookup & reference functions such as the VLOOKUP, HLOOKUP, CHOOSE, MATCH and INDEX function. Navigation: Formula Tab → Function Library Group → Lookup and Reference Vlookup VLOOKUP stands for Vertical lookup. The VLOOKUP function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column …
Excel has many functions to offer when it comes to manipulating text strings. Formula tab → Function Library Group → Text Function N/B: Every formula in excel must begin with an equal sign, function then in bracket; cell name/cell ranges/cell references. Syntax: =fx(cell name/cell ranges/cell references) Example: = Sum( A1:B4) Where Sum is the function name and A1:B4 is …
To enter a date in Excel, use the “/” or “-” characters. To enter a time, use the “:” (colon). You can also enter a date and a time in one cell. Note: Dates are in US Format. Months first, Days second. This type of format depends on your windows regional settings. Learn more about Date and Time formats. Year, …