Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms. This article covers the most popular functions in Excel referenced by different sites such as Microsoft, wikipedia and search engines.
Most Popular Functions in Excel:
A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel.
It is one of the math and trig functions, adds values. You can add individual values, cell references or ranges or a mix of all three.
Argument Name Description number1 (required) The first number you want to add. The number can be like 4, a cell reference like B6, or a cell range like B2:B8. number2-255 (Optional) This is the second number you want to add. You can specify up to 255 numbers in this way.12=SUM(A2,A3,A4) //calculates the sum of three numbers in three cells=SUM(A2:A10, C2:C10) // calculates the sum of two ranges, each containing 9 cells.
The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect. In its simplest form, the IF function says:
IF(Something is True, then do something, otherwise do something else)
So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.
Syntax:1=IF(logical_test, value_if_true, [value_if_false])
Argument Name Description logical_test (required) The condition you want to test. value_if_true (required) The value that you want returned if the result of logical_test is TRUE. value_if_false (optional) TThe value that you want returned if the result of logical_test is FALSE.
In the above example, if the cell C2 has “Ok” in it so the if function in cell D2 returns 1 otherwise 0.
In the above example, if the cell C2 has 1 in it so the if function in cell D2 returns Yes otherwise No.
It looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range.Syntax:1=LOOKUP(lookup_value, lookup_vector, [result_vector])
The LOOKUP function vector form syntax has the following arguments:
lookup_value Required. A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.
lookup_vector Required. A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values.
Important: The values in lookup_vector must be placed in ascending order: …, -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.
result_vector Optional. A range that contains only one row or column. The result_vector argument must be the same size as lookup_vector. It has to be the same size.
Example: Concentrate on the example below, Looks up day in column A, and returns the value from column B that is in the same row.
Vlookup is one of the beneficent and most used functions in excel. You can use it to lookup for a value in a table by row.
In simple form, the VLOOKUP function says:
VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE).
1=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Argument Name Description lookup_value required The value you want to look up. The value you want to look up must be in the first column of the range of cells you specify in table-array .
For example, if table-array spans cells B2:D7, then your lookup_value must be in column B. See the graphic below. Lookup_value can be a value or a reference to a cell.
table_array required The range of cells in which the VLOOKUP will search for the lookup_value and the return value.
The first column in the cell range must contain the lookup_value (for example, Last Name in the picture below.) The cell range also needs to include the return value (for example, First Name in the graphic below) you want to find.
col_index_num required The column number (starting with 1 for the left-most column of table-array) that contains the return value. range_lookup optional A logical value that specifies whether you want VLOOKUP to find an approximate or an exact match:
TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don’t specify one.
FALSE searches for the exact value in the first column.
Examples: The descriptions are inside the pictures. (pictures from microsoft examples)
The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.
1=MATCH(lookup_value, lookup_array, [match_type])
lookup_value Required. The value that you want to match in lookup_array. For example, when you look up someone’s number in a telephone book, you are using the person’s name as the lookup value, but the telephone number is the value you want.
The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
lookup_array Required. The range of cells being searched.
match_type Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.
Concentrate on the below example with it’s description:
Stay tuned with codejow!