Top 10 Excel functions | wps office tutorial
Hello, I Am Balram Singh Welcome to Our YouTube Channel.
Aio Digital Institute Provides Computer Education to The Students Through Live and Tutorials Videos.
1. VLOOKUP Function
The VLOOKUP function in Excel allows you to look up a value in a table and return a corresponding value from another column. The basic syntax is:
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
where:
lookup_value is the value you want to find in the table.
table_array is the range of cells that contains the table.
col_index_num is the column number in the table that contains the value to return.
range_lookup is an optional argument that specifies whether you want an exact match (FALSE) or an approximate match (TRUE)
2. SUBTOTAL function
The SUBTOTAL function in Excel allows you to perform a calculation on a range of cells while ignoring hidden or filtered rows. The basic syntax is:
=SUBTOTAL (function_num, ref1, [ref2], …)
where:
function_num is a number that specifies which function to use, such as SUM, AVERAGE, COUNT, MAX, etc.
ref1, ref2, … are the ranges of cells to include in the calculation.
3. Average function
The AVERAGE function in Excel calculates the average (arithmetic mean) of a group of numbers. The basic syntax is:
=AVERAGE (number1, [number2], …)
where:
number1, number2, … are the numbers or ranges of cells to include in the calculation.
AVERAGE ignores logical values, empty cells and cells that contain text.
4. Sumif function
The SUMIF function in Excel allows you to sum the values in a range that meet a single criterion that you specify. The basic syntax is:
=SUMIF (range, criteria, [sum_range])
where:
range is the range of cells that you want to apply the criterion to
criteria are the condition that defines which cells to sum.
sum_range is the range of cells to sum, if different from range.
5. If function
The IF function in Excel allows you to make a logical comparison between a value and what you expect, and return one value if the comparison is true and another value if the comparison is false. The basic syntax is:
=IF(logical_test, value_if_true, [value_if_false])
where:
logical_test is the condition that you want to check
value_if_true is the value that you want to return if the condition is true
value_if_false is the value that you want to return if the condition is false, or blank if omitted
6. Len Function
The LEN function in Excel is used to count the number of characters in a cell, including letters, numbers, spaces, and special characters. The basic syntax is:
=LEN(text)
where:
text is the text whose length you want to find, or a reference to a cell that contains text
LEN returns a number as the result, and if the text is an empty string or a reference to an empty cell, LEN returns zero.
7. Count function
The COUNT function in Excel is used to count the number of cells that contain numbers, and counts numbers within the list of arguments. The basic syntax is:
=COUNT(value1, [value2], …)
where:
value1 is the first item, cell reference, or range within which you want to count numbers
value2, … are optional additional items, cell references, or ranges within which you want to count numbers
COUNT returns a number as the result, and ignores empty cells and text values.
8. Counta function
The COUNTA function in Excel is used to count the number of cells that are not empty in a range or a cell reference. The basic syntax is:
=COUNTA(value1, [value2], …)
where:
value1 is the first item, cell reference, or range within which you want to count non-empty cells
value2, … are optional additional items, cell references, or ranges within which you want to count non-empty cells
COUNTA returns a number as the result, and counts cells containing any type of information, including text, numbers, errors, logical values, or empty strings.
9. Trim Function
The TRIM function in Excel removes all spaces from text except for single spaces between words, and removes any leading or trailing space. The basic syntax is:
=TRIM(text)
where:
text is the text that you want to trim, or a reference to a cell that contains text
TRIM returns a text value as the result, and does not affect the original text.
10. Iferror function
The IFERROR function in Excel returns a custom result when a formula generates an error, and a standard result when no error is detected. The basic syntax is:
=IFERROR(value, value_if_error)
where:
value is the argument that is checked for an error.
value_if_error is the value to return if the formula evaluates to an error.
IFERROR returns a value as the result, and can handle any error type.
Download Practice File link--
https://drive.google.com/file/d/1oVXo...
#excel #msexcel #exceltips
Contact
Email - [email protected]
must know excel functions,top 10 formula excel