Buzz
However, to expand your knowledge of Excel, today Mytour will share incredibly useful functions in Excel, helping you save time and improve work efficiency.
- How to Wrap Text in Excel for Windows and MacOS
- Step-by-Step Guide to Inserting Images into Excel Easily
Essential Things to Know about Excel Functions
Before performing and applying any formulas for data analysis or calculations, you need to understand the basic functions in Excel as follows:- All formula functions in Excel spreadsheets begin with the equals sign '='.
- After the equals sign '=', enter the cell or formula function. This function specifies the type of formula for the spreadsheet.
- If a mathematical function is being executed, the mathematical formula is enclosed in parentheses.
- Use a colon ':' to receive a range of cells for the formula. For example, A1:A10 represents cells from A1 to A10.
- By default, formulas are created using relative cell references. If you add a dollar sign '$' before a column or row, that formula becomes an absolute cell reference.
Basic Excel Functions You Need to Remember
Sum Function
The Sum function is the most basic function that anyone starting to use Excel must know. The Sum formula allows you to calculate the sum of 2 or more different values. To perform, you enter the syntax as follows: =SUM(value 1, value 2,...). Note: The values here are real numbers or numbers in specific cells in the spreadsheet.
Conditional SUMIF Function
To perform a conditional sum operation (e.g., greater than 100), use the SUMIF function below (two arguments). The formula for the Sumif function is as follows: =SUMIF(range, criteria,[sum_range]) In summary:- Range: The range of cells containing data.
- Criteria: The condition/value that needs to be met. Conditions can be provided as (numbers, text, dates, logical expressions, character expressions, or other Excel functions).
And the result is 650 after executing the formula and pressing Enter.AutoSum Function
SUM is perhaps the most commonly used function in Microsoft Excel. However, many users input this function manually, which is not very efficient. Instead, you can utilize the option for automatic summing available on the formula bar or simply use the shortcut 'Alt + ='.AutoSum function to guess the range is very smart and you can easily adjust the range within the function.
Count Function
If you have a spreadsheet containing a vast database and you want to know how many cells are in a certain range or in a spreadsheet containing only numerical sequences, without letters. To save time compared to manual methods, use the function
=COUNTto count the values and quantities within the desired range. The COUNT formula in Excel is as follows:
=COUNT(value1, [value2], ...).For example, if you need to count from cell C1 to C11, enter
=COUNT(C1:C11).In which:- Value1: Refers to the cell or range selection to be counted. This is a mandatory value.
- Value2: Refers to additional cells or range selections to be counted. Up to 255 items maximum. This is an optional value.
COUNTIF Conditional Function
To count specific condition-based cells (e.g., greater than 50), use the COUNTIF function in Excel as follows:
=COUNTIF(range, criteria).In which:- Range: The range of conditions we want to count or analyze.
- Criteria: The condition we want to get the result from.
Subtraction
To perform subtraction in Excel, select the cell for subtraction and use the SUM function formula or use formatting.
Example:Calculate the difference between 66 and 10
Multiplication
Using the basic multiplication function in Excel, perform the formula in the format, these functions
use the asterisk symbol '*'to represent multiplication.
Division
In Excel, division is one of the functions in Excel that you can easily perform. To perform division, select an empty cell, then enter the equals sign '=', you select the value you want to divide,
use the forward slash symbol '/'to separate between values.
MIN, MAX Function
To find the minimum value within a data range, you use the function =MIN. Perform the MIN function formula as follows:
=MIN(Range), consisting of addresses from the first cell to the last cell.
On the contrary, if you want to find the largest value within a range, use the function =MAX. The syntax is similar to the =MIN function.
AVERAGE Function calculates the average value
The Average function is used to provide the average amount of selected numbers.Function syntax: = AVERAGE (Number1, Number2...) and press Enter.In which:- Number1: The first parameter can be a number, a cell reference, or a range containing numbers to be averaged. This is a mandatory parameter.
- Number2: This is a number, a cell reference, or a range containing additional numbers. You want to average up to 255 numbers, optional.
IF Function
The IF function is used to check whether your condition is met or not, if the condition is true, it will return true value, if false it will return false value.
IF function formula: =IF(condition,'true value','false value').Below is an example to help you check whether cell A meets the condition of greater than 50 or not. If it satisfies, Correct value will be returned to cell C1, if it returns Incorrect value then it does not meet the condition. In cell C1, you enter the function as follows:
=IF(A1>50,'Correct','Incorrect').
The IF function returns Correct result because cell A1 is greater than 50.
AND Function
If all conditions are met, the AND function will return True, if any condition is False, it will return False. Formula: = IF(AND (condition),'true value','false value)For example, now we combine the test of A1 greater than 50 and B1 greater than 25 two conditions, if they both are met then we return Correct, if not we return Incorrect. Enter =IF(AND(A1>50,B1>25),'Correct','Incorrect') into cell D1.
OR Function
In contrast to AND, the OR function returns true if any condition is met and false if all conditions are not met. Use the OR function to retry the conditions above. Enter the function: = IF(OR(A1>50,B1>25),'Correct','Incorrect') in cell E1.
The OR function returns true because cell A1 is greater than 50, therefore, the IF function returns the correct value as Correct.General note: The AND and OR functions can check a maximum of 255 conditions.COUNTA Function counts non-empty cells
The COUNTA function performs the function of counting the number of cells containing content with any digits, letters, or symbols, understood differently as a function used to count blank cell ranges. Syntax: =COUNTA(range of spreadsheet to count).
Summary of basic functions in Excel
Above is the article guiding the use of basic functions in Excel, the simplest. Hope this article will help you grasp many basic functions in Excel. Wish you success!
- See also:Basic Excel tricks
BUY LAPTOP AT SUPER GOOD PRICE
