Are you a person who regularly uses Microsoft Excel for your work purposes? So do you know the functions in Excel or not?

To help you work faster, the following article will provide the most complete statistics of the basic functions in Microsoft Excel .

1. Logical function

• AND function:
  • Syntax: AND (Logical1, Logical2, ….)
  • The arguments: Logical1, Logical2… are conditional expressions.
  • The function returns TRUE (1) if all its arguments are true, and FALSE (0) if one or more of its arguments are false.

Note:

– Arguments must be logical values or arrays or references containing logical values.

– If the reference argument is a text or null value, those values are ignored.

– If the reference area does not contain a logical value, the function returns the #VALUE!

Example :

AND(D7>0,D7<5000)

• OR function:
  • Syntax: OR (Logical1, Logical2…)
  • The arguments: Logical1, Logical2… are conditional expressions.
  • The function returns TRUE (1) if any of its arguments are true, and FALSE (0) if all of its arguments are false.

For example:

OR(F7>03/02/74,F7>01/01/2002)

• NOT function:
  • Syntax: NOT(Logical)
  • Argument: Logical is a value or a logical expression.
  • The function reverses the value of the argument. Use NOT when you want to negate the value of the argument in this operation.

2. Math function

• ABS function: Get the absolute value of a number
  • Syntax: ABS(Number)
  • Arguments: Number is a numeric value, a reference, or an expression.

For example:

ABS(A5 + 5)

• POWER function: Returns the power of a number.
  • Syntax: POWER(Number, Power)
  • Parameters: Number: Is a real number that you want to get the power.
  • Power: Is the exponent.

For example:

POWER(5,2) = 25

• PRODUCT function:
  • You can use the PRODUCT function instead of the * multiplication operator to calculate the product of an array.
  • Syntax: PRODUCT(Number1, Number2…)
  • The parameters: Number1, Number2… are the numbers you want to multiply.
• MOD function:
  • Get the remainder of the division.
  • Syntax: MOD(Number, pisor)
  • Arguments: Number: Number to be divided.
  • pisor: Divisor.
• ROUNDUP function:
  • Round a number.
  • Syntax: ROUNDUP(Number, Num_digits)
  • Parameters: Number: Is a real number that you want to round up.
  • Number_digits: is the number of decimal places you want to round.

Attention:

– If Num_digits > 0 will round the decimal part.
– If Num_digits = 0 will round to the nearest natural number.
– If Num_digits < 0 will round the integer after the decimal point.

• EVEN function:
  • Round up to the nearest even integer.
  • Syntax: EVEN(Number)
  • parameter: Number is the number you want to round.

Attention:

– If Number is not a numeric type, the function returns the #VALUE!

• ODD function:
  • Round up to the nearest odd integer.
  • Syntax: ODD(Number)
  • Parameter: Number is the number you want to round.
• ROUNDDOWN function:
  • Round down a number.
  • Syntax: ROUNDDOWN(Number, Num_digits)
  • Parameters: same as ROUNDUP function.

3. Sum function

• SUM . function
  • Adds all numbers in a selected range.
  • Syntax: SUM(Number1, Number2…)
  • The parameters: Number1, Number2… are the numbers to be summed.
• SUMIF . function
  • Sums the cells specified by the input criteria.
  • Syntax: SUMIF(Range, Criteria, Sum_range)
  • Parameters:
    • Range: The range you want to define.
    • Criteria: the criteria that you want to sum. This criterion can be a number, expression or string.
    • Sum_range: The cells that actually need to be summed.

4. Average function

• AVERAGE function:
  •   Returns the average value of the arguments.
  • Syntax: AVERAGE(number1, number2…)
  • The parameters: number1, number2 … are the numbers to calculate the average value.

For example:

Formula E6=AVERAGE(C6:D6)

• SUMPRODUCT . function
  • Take the product of the input sequences, then sum those products
  • Syntax : SUMPRODUCT(Array1, Array2, Array3…)
  • The parameters: Array1, Array2, Array3… are the ranges of cells that you want to multiply and then sum the products.

Attention

The arguments in the arrays must be in the same direction. Otherwise the function will return the #VALUE . error value

5. Value function

• MAX . function
  • Returns the largest number in the entered range.
  • Syntax: MAX-(Number1, Number2…)
  • Parameters : Number1, Number2… is the range in which you want to find the largest value
• LAGR function
  • Find the kth largest number in an entered sequence.
  • Syntax: LARGE(Array, k)
  • Parameters:
    • Array: Is an array or a data area.
    • k: Is the rank of the number you want to find from the largest number in the sequence.
• MIN . function
  • Returns the smallest number in the entered range.
  • Syntax: MIN(Number1,Number2…)
  • Parameters: Number1, Number2… is the range in which you want to find the smallest value.
• SMALL . function
  • Find the kth smallest number in an entered sequence.
  • Syntax : SMALL(Array, k)
  • Parameters:
    • Array: Is an array or an area of data.
    • k: Is the rank of the number you want to find since the smallest number in the sequence

6. Data count function

• COUNT function:
  • The COUNT function counts cells that contain numeric data in the range.
  • Syntax: COUNT(Value1, Value2, …)
  • The parameters : Value1, Value2… are arrays or ranges of data.
• COUNTA function:
  • Count all cells containing data.
  • Syntax: COUNTA(Value1, Value2, …)
  • The parameters : Value1, Value2… are arrays or ranges of data.
• COUNTIF function:
  • The COUNTIF function counts cells containing numeric values according to a given condition.
  • Syntax: COUNTIF(Range, Criteria)
  • Parameters :
    • Range: The range of data you want to count.
    • Criteria: Is the standard for cells to be counted.

For example:

COUNTIF(B3:B11,”>100″): (Counts all cells in range B3:B11 that contain numbers greater than 100)

7. String Functions

• LEFT function:
  • Extract the characters to the left of the input string.
  • Syntax : LEFT(Text,Num_chars)
  • Arguments :
    • Text: String of text.
    • Num_Chars: Number of characters you want to extract.

For example:

LEFT(My name is,3) = “I”

• RIGHT function:
  • Extract the characters to the right of the input string.
  • Syntax: RIGHT(Text,Num_chars)
  • Arguments : same as LEFT function.

For example:

RIGHT(My name is,2) = “is”

• MID function:
  • Extract characters from the number starting in the input string.
  • Syntax: MID(Text,Start_num, Num_chars)
  • Arguments :
    • Text: text string.
    • Start_num: The ordinal number of the starting character to be extracted.
    • Num_chars: Number of characters to extract.

• UPPER function:
  • Change the input string to uppercase.
  • Syntax: UPPER(Text)
• LOWER function:
  • Convert the input string to lowercase.
  • Syntax: LOWER(Text)
• PROPER function:
  • Change the first character of the word in the string to uppercase.
  • Syntax: PROPER(Text)

For example:

PROPER(Valve valve a) = “Valve valve A”

• TRIM function:
  • Trim off whitespace characters at the beginning and end of the string.
  • Syntax: TRIM(Text)

6. Date function

• DATE function:
  • The Date function returns a string representing a particular date type.
  • Syntax: DATE(year,month,day)
  • Parameters:
    • Year: describes the year, can be from 1 to 4 digits. If you enter 2 digits, by default Excel will take the starting year as: 1900.(Example)
    • Month: describe the month of the year. If month is greater than 12, then Excel will automatically add months to the number describing the year.
    • Day: describe the day of the month. If Day is greater than the number of days in the specified month, Excel will automatically add days to the number describing the month.

Note:

– Excel stores the date type as a continuous series of numbers, so the addition (+) and subtraction (-) operations can be used for the date type.(Example)

• DAY function:
  • Returns the date corresponding to the input date string. The return value is an Integer number between 1 and 31.
  • Syntax: DAY(Serial_num)
  • Parameter: Serial_num: Data of type Date, can be a DATE function or the result of another function or formula.
• MONTH function:
  • Returns the month of the described date string. The return value is a number between 1 and 12.
  • Syntax: MONTH(Series_num)
  • Parameter: Series_num: A string of dates, which can be a DATE function or the result of another function or formula.
• YEAR function:
  • Returns the year corresponding to the input date string. Year is returned as an Integer in the range 1900-9999.
  • Syntax: YEAR(Serial_num)
  • Parameter : Serial_num: A date data type, which can be a DATE function or the result of another function or formula
• TODAY function:
  • Returns the current system date.
  • Syntax : TODAY()
  • This function has no arguments.
• WEEKDAY function:
  • Returns the number of the day of the week.
  • Syntax: WEEKDAY(Serial, Return_type)
  • Arguments:
    • Serial: a number or date value.
    • Return_type: Specifies the return data type.

7. Time function

• TIME function:
  • Returns a string representing a particular time type. The return value is a number between 0 and 0.9999999, representing the time from 0:00:00 to 23:59:59.
  • Syntax : TIME(Hour,Minute,Second)
  • Parameters: Calculated similarly in the DATE function.
    • Hour: describes the hour, is a number from 0 to 32767.
    • Minute: describes minutes, is a number from 0 to 32767.
    • Second: describes seconds, is a number from 0 to 32767.

HOUR function:

• Returns the hour of the day of the input hour data type. The return value is an Integer between 0 (12:00A.M) and 23 (11:00P.M).
• Syntax : HOUR(Serial_num)
• Parameter : Serial_num: Is data type Time. Time can be entered as:
  • – A string of characters enclosed in quotes (e.g. “5:30 PM”)
  • – A decimal number (e.g. 0.2145 describes 5:08 AM)
  • – The result of another formula or function.
• MINUTE function:
  • Returns the minutes of the input Time data type. The return value is an Integer between 0 and 59.
  • Syntax: MINUTE(Serial_num)
  • Parameter: Serial_num: Same as in HOUR formula.
• SECOND function:
  • Returns the seconds of the input Time data type. The return value is an Integer between 0 and 59.
  • Syntax : SECOND(Serial_num)
  • Parameter: Serial_num: Same as in HOUR formula.
• NOW function:
  • Returns the current system date and time.
  • Syntax p: NOW()
  • This function has no arguments.

7. Some other functions

• VLOOKUP function:
  • Finds another value in a row by comparing it with the values in the first column of the input table.
  • Syntax: VLOOKUP(Lookup Value, Table array, Col idx num, [range lookup])
  • Parameters:
    • – Lookup Value: Value to be compared to find.
    • – Table array: The table contains information that the data in the table is data to compare. This data area must be an absolute reference.
    • – If the Range lookup value is TRUE or omitted, the values in the column to be compared must be sorted ascending.
    • – Col idx num: the number indicating the column of data that you want to get in the comparison.
    • – Range lookup: Is a logical value to specify for the VLOOKUP function to find the exact value or find the approximate value. + If Range lookup is TRUE or omitted, the approximate value is returned.

Attention:

– If the Lookup value is less than the smallest value in the first column of the Table array table, it will report #N/A error.

– Example: =VLOOKUP(F11,C20:D22,2,0)

– Find a value equal to the value in cell F11 in the first column, and get the corresponding value in the second column.

• HLOOKUP function:
  • Search is similar to the VLOOKUP function but by comparing it with the values in the first row of the input table.
  • Syntax : HLOOKUP(Lookup Value, Table array, Col idx num, [range lookup])
  • The parameters are similar to the VLOOKUP function.
• INDEX function:
  • Returns a value or a reference to a value in the table range or data range.
  • Syntax : INDEX(Array,Row_num,Col_num)
  • Parameters:
    • – Array: Is an array of cells or an immutable array.
    • – If the Array contains only one row and one column, the corresponding Row_num or Col_num parameter is optional.
    • – If Array has more than one row or one column, only one Row_num or Col_num is used.
    • – Row_num: Select rows in Array. If Row_num is omitted then Col_num is required.
    • – Col_num: Select columns in Array. If Col_num is omitted then Row_num is required.

Good luck!