Excel functions
There are 6 types of functions in excel.
Those are
- Mathematical functions
- Statistical functions
- Date and Time functions
- Text functions
- Count Functions
- MATHEMATICAL FUNCTIONS
- SUM ():-
The SUM function in Excel is a fundamental tool for adding up numbers in a range of cells. It’s incredibly useful for calculating totals, such as summing up sales figures, expenses, or any other numerical data.
Syntax:
=SUM(number1, [number2], …)
Arguments:
- number1, number2, …: These are the numbers you want to add. You can input individual numbers, cell references, or ranges of cells.
- ABS ():-
The ABS Function in Excel
The ABS function in Excel returns the absolute value of a number. This means it converts any negative number to a positive number, while positive numbers remain unchanged.
Syntax:
Excel
=ABS(number)
Use code with caution.
Argument:
- number: The number for which you want to calculate the absolute value.
Example:
If you have the number -5 in cell A1, you can use the following formula to get its absolute value:
Excel
=ABS(A1)
Use code with caution.
This will return 5 in the cell where you enter the formula.
- EVEN ():-
he EVEN Function in Excel
The EVEN function in Excel rounds a number up to the nearest even integer.
Syntax:
Excel
=EVEN(number)
Use code with caution.
Argument:
- number: The number you want to round.
- ODD ():-
The ODD function in Excel is a mathematical function that rounds a given number up to the nearest odd integer.
Syntax:
Excel
=ODD(number)
Use code with caution.
Argument:
- number: The number you want to round.
- PRODUCT ():-
The PRODUCT function in Excel is used to multiply multiple numbers together. It’s a convenient way to calculate the product of a range of cells or specific numbers.
Syntax:
Excel
=PRODUCT(number1, [number2], …)
Use code with caution.
Arguments:
- number1, number2, …: These are the numbers you want to multiply. You can input individual numbers, cell references, or ranges of cells.
- PI ():-
The PI Function in Excel
Excel provides a built-in function to access the mathematical constant Pi (π), which is approximately 3.14159. This function is particularly useful for calculations involving circles, spheres, and other geometric shapes.
Syntax:
Excel
=PI()
- FACT ():-
The FACT function in Excel calculates the factorial of a number. The factorial of a non-negative integer n, denoted as n!, is the product of all positive integers less than or equal to n.
Syntax:
Excel
=FACT(number)
Argument:
- number: The non-negative integer for which you want to calculate the factorial.
Example:
To calculate the factorial of 5 (5!), you would use the following formula:
Excel
=FACT(5)
- POWER ():-
The POWER function in Excel calculates the result of a number raised to a given power. It’s a versatile function for various mathematical and scientific calculations.
Syntax:
Excel
=POWER(number, power)
Use code with caution.
Arguments:
- number: The base number.
- power: The exponent to which the base number is raised.
Example:
To calculate 2 raised to the power of 3 (2^3), you would use the following formula:
Excel
=POWER(2, 3)
- SQRT ():-
The SQRT function in Excel calculates the square root of a given number.
Syntax:
Excel
=SQRT(number)
Use code with caution.
Argument:
- number: The number for which you want to calculate the square root.
Example:
To find the square root of 25, you would use the following formula:
Excel
=SQRT(25)
- STATISTICAL FUNCTIONS
Statistical Functions in Excel: A Comprehensive Guide
Excel offers a robust suite of statistical functions that can help you analyze and interpret your data. Here are some of the most commonly used ones, along with examples:
Basic Statistical Functions:
- AVERAGE: Calculates the average of a range of numbers.
- Syntax: =AVERAGE(range)
- Example: =AVERAGE(A1:A10)
- COUNT: Counts the number of cells in a range that contain numbers.
- Syntax: =COUNT(range)
- Example: =COUNT(B2:B15)
- COUNTA: Counts the number of cells in a range that contain any value, including text and numbers.
- Syntax: =COUNTA(range)
- Example: =COUNTA(C3:C20)
- COUNTIF: Counts the number of cells in a range that meet a given criterion.
- Syntax: =COUNTIF(range, criteria)
- Example: =COUNTIF(D4:D25, “>10”)
- MAX: Returns the largest value in a range.
- Syntax: =MAX(range)
- Example: =MAX(E1:E12)
- MIN: Returns the smallest value in a range.
- Syntax: =MIN(range)
- Example: =MIN(F5:F20)
- MEDIAN: Returns the median (middle value) of a range.
- Syntax: =MEDIAN(range)
- Example: =MEDIAN(G1:G15)
- MODE: Returns the most frequent value in a range.
- Syntax: =MODE(range)
- Example: =MODE(H2:H18)
- SUM: Adds up the values in a range.
- Syntax: =SUM(range)
- Example: =SUM(I3:I22)
DATE AND TIME FUNCTIONS
Date and Time Functions in Excel
Excel provides a variety of functions to manipulate dates and times. Here are some of the most commonly used ones:
Date Functions:
- TODAY(): Returns the current date.
- Example: =TODAY()
- NOW(): Returns the current date and time.
- Example: =NOW()
- DATE(year, month, day): Creates a date.
- Example: =DATE(2023, 11, 25)
- YEAR(date): Extracts the year from a date.
- Example: =YEAR(A2) (where A2 contains a date)
- MONTH(date): Extracts the month from a date.
- Example: =MONTH(A2)
- DAY(date): Extracts the day from a date.
- Example: =DAY(A2)
- WEEKDAY(date, [return_type]): Returns the day of the week.
- Example: =WEEKDAY(A2, 2) (returns 2 for Monday, 3 for Tuesday, etc.)
Time Functions:
- TIME(hour, minute, second): Creates a time.
- Example: =TIME(10, 30, 0)
- HOUR(time): Extracts the hour from a time.
- Example: =HOUR(B2)
- MINUTE(time): Extracts the minute from a time.
- Example: =MINUTE(B2)
- SECOND(time): Extracts the second from a time.
- Example: =SECOND(B2)
- TEXT FUNCTIONS
Text Functions in Excel
Excel provides a rich set of text functions to manipulate and format text strings. Here are some of the most commonly used ones:
Basic Text Functions:
- LEN: Returns the length of a text string.
- Example: =LEN(“Hello, World!”)
- UPPER: Converts text to uppercase.
- Example: =UPPER(“hello”)
- LOWER: Converts text to lowercase.
- Example: =LOWER(“HELLO”)
- PROPER: Capitalizes the first letter of each word in a text string.
- Example: =PROPER(“hello world”)
- CONCATENATE: Combines multiple text strings into one.
- Example: =CONCATENATE(“Hello”, ” “, “World”)
- LEFT: Extracts a specified number of characters from the left side of a text string.
- Example: =LEFT(“Hello, World!”, 5)
- RIGHT: Extracts a specified number of characters from the right side of a text string.
- Example: =RIGHT(“Hello, World!”, 5)
- MID: Extracts a specified number of characters from a text string, starting at a specified position.
- Example: =MID(“Hello, World!”, 7, 5)
Text Search and Replacement Functions:
- FIND: Finds the starting position of one text string within another.
- Example: =FIND(“World”, “Hello, World!”)
- SEARCH: Similar to FIND, but case-insensitive.
- Example: =SEARCH(“world”, “Hello, World!”)
- SUBSTITUTE: Replaces specific text within a text string.
- Example: =SUBSTITUTE(“Hello, World!”, “World”, “Universe”)
- COUNT FUNCTIONS
Count Functions in Excel
Excel provides several functions to count cells based on specific criteria. Here are the most commonly used ones:
Basic Counting Functions:
- COUNT: Counts the number of cells in a range that contain numbers.
- Syntax: =COUNT(range)
- Example: =COUNT(A1:A10)
- COUNTA: Counts the number of cells in a range that contain any value, including text and numbers.
- Syntax: =COUNTA(range)
- Example: =COUNTA(B2:B15)
Conditional Counting Functions:
- COUNTIF: Counts the number of cells in a range that meet a specific criterion.
- Syntax: =COUNTIF(range, criteria)
- Example: =COUNTIF(C4:C25, “>10”)
- COUNTIFS: Counts the number of cells in a range that meet multiple criteria.
- Syntax: =COUNTIFS(range1, criteria1, range2, criteria2, …)
- Example: =COUNTIFS(D2:D10, “>10”, E2:E10, “<20”)
Other Counting Functions:
- COUNTBLANK: Counts the number of empty cells in a range.
- Syntax: =COUNTBLANK(range)
- Example: =COUNTBLANK(F1:F20)
- COUNTIFS: Counts the number of cells in a range that meet multiple criteria.
- Syntax: =COUNTIFS(range1, criteria1, range2, criteria2, …)
- Example: =COUNTIFS(G2:G10, “>10”, H2:H10, “<20”)
By effectively using these count functions, you can efficiently analyze and summarize data in Excel.
