Excel offers a vast array of formulas and functions to help you perform a wide range of calculations and data analysis tasks. Here’s a comprehensive list of the various types of formulas and functions available in Excel:
Basic Arithmetic Operations
- `+`: Addition
- `-`: Subtraction
- `*`: Multiplication
- `/`: Division
- `^`: Exponentiation
Basic Functions
- `SUM(range)`: Adds all numbers in a range of cells.
- `AVERAGE(range)`: Calculates the average of numbers in a range.
- `COUNT(range)`: Counts the number of cells that contain numbers.
- `COUNTA(range)`: Counts the number of non-empty cells.
- `MIN(range)`: Finds the minimum value in a range.
- `MAX(range)`: Finds the maximum value in a range.
Logical Functions
- `IF(logical_test, value_if_true, value_if_false)`: Performs a logical test and returns one value for a TRUE result and another for a FALSE result.
- `AND(logical1, [logical2], ...)`: Returns TRUE if all arguments are TRUE.
- `OR(logical1, [logical2], ...)`: Returns TRUE if any argument is TRUE.
- `NOT(logical)`: Reverses the value of its argument.
Lookup and Reference Functions
- `VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`: Vertical lookup for a value in the first column of a table and returns a value in the same row from a specified column.
- `HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])`: Horizontal lookup for a value in the first row of a table and returns a value in the same column from a specified row.
- `INDEX(array, row_num, [column_num])`: Returns the value of an element in a table or an array, selected by the row and column number indexes.
- `MATCH(lookup_value, lookup_array, [match_type])`: Searches for a value in an array and returns the relative position of that item.
Text Functions
- `CONCATENATE(text1, [text2], ...)`: Joins several text items into one text item.
- `LEFT(text, [num_chars])`: Returns the leftmost characters from a text value.
- `RIGHT(text, [num_chars])`: Returns the rightmost characters from a text value.
- `MID(text, start_num, num_chars)`: Returns a specific number of characters from a text string, starting at the position you specify.
- `LEN(text)`: Returns the number of characters in a text string.
- `TRIM(text)`: Removes all spaces from text except for single spaces between words.
Date and Time Functions
- `NOW()`: Returns the current date and time.
- `TODAY()`: Returns the current date.
- `DATE(year, month, day)`: Returns the serial number of a particular date.
- `DAY(serial_number)`: Converts a serial number to a day of the month.
- `MONTH(serial_number)`: Converts a serial number to a month.
- `YEAR(serial_number)`: Converts a serial number to a year.
Financial Functions
- `PMT(rate, nper, pv, [fv], [type])`: Calculates the payment for a loan based on constant payments and a constant interest rate.
- `FV(rate, nper, pmt, [pv], [type])`: Returns the future value of an investment based on periodic, constant payments and a constant interest rate.
- `PV(rate, nper, pmt, [fv], [type])`: Returns the present value of an investment.
Statistical Functions
- `AVERAGE(range)`: Calculates the average of numbers.
- `MEDIAN(range)`: Returns the median of the given numbers.
- `MODE.SNGL(range)`: Returns the most frequently occurring value in a range.
- `STDEV.P(range)`: Calculates the standard deviation based on the entire population.
- `VAR.P(range)`: Calculates the variance based on the entire population.
Math and Trigonometry Functions
- `ABS(number)`: Returns the absolute value of a number.
- `ROUND(number, num_digits)`: Rounds a number to a specified number of digits.
- `INT(number)`: Rounds a number down to the nearest integer.
- `SUMPRODUCT(array1, [array2], ...)`: Returns the sum of the products of corresponding ranges or arrays.
Array Formulas
- `TRANSPOSE(array)`: Returns the transpose of an array.
- `FREQUENCY(data_array, bins_array)`: Calculates how often values occur within a range of values, and then returns a vertical array of numbers.
Engineering Functions
- `CONVERT(number, from_unit, to_unit)`: Converts a number from one measurement system to another.
Database Functions
- `DSUM(database, field, criteria)`: Adds the numbers in the field column of records in the database that match the criteria.
Information Functions
- `ISNUMBER(value)`: Checks if a value is a number.
- `ISBLANK(value)`: Checks if a cell is empty.
These are just some of the most commonly used functions in Excel. There are many more specialized functions available for different types of data analysis and manipulation.
कोई टिप्पणी नहीं:
एक टिप्पणी भेजें