Excel is a powerful spreadsheet software that offers various formulas for calculations and data analysis:
BASIC FORMULAS
- 1. SUM:
-
Description: Adds up a range of numbers.
Syntax: =SUM(number1, [number2], …)
- 2. AVERAGE:
-
Description: Calculates the average of a range of numbers.
Syntax: =AVERAGE(number1, [number2], …)
- 3. MAX:
-
Description: Returns the largest number in a range.
Syntax: =MAX(number1, [number2], …)
- 4. MIN:
-
Description: Returns the smallest number in a range.
Syntax: =MIN(number1, [number2], …)
- 5. COUNT:
-
Description: Counts the number of cells that contain numbers.
Syntax: =COUNT(value1, [value2], …)
- 6. IF:
-
Description: Returns one value if a condition is true and another if false.
Syntax: =IF(logical_test, value_if_true, value_if_false)
- 7. CONCATENATE:
-
Description: Combines multiple text strings into one.
Syntax: =CONCATENATE(text1, [text2], …)
- 8. DATE:
-
Description: Creates a date value based on year, month, and day.
Syntax: =DATE(year, month, day)
ADVANCED FORMULAS
- 1. VLOOKUP:
-
Description: Searches for a value in a table and returns a corresponding value.
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- 2. HLOOKUP:
-
Description: Searches for a value in a table and returns a corresponding value from a horizontal table.
Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- 3. INDEX:
-
Description: Returns the value of a cell at a specified row and column in a range.
Syntax: =INDEX(array, row_num, [column_num])
- 4. MATCH:
-
Description: Searches for a specified value in a range and returns its relative position.
Syntax: =MATCH(lookup_value, lookup_array, [match_type])
- 5. SUMIF:
-
Description: Adds up values that meet a single condition.
Syntax: =SUMIF(range, criteria, [sum_range])
- 6. COUNTIF:
-
Description: Counts the number of cells that meet a single condition.
Syntax: =COUNTIF(range, criteria)
- 7. IFERROR:
-
Description: Returns a specified value if a formula results in an error.
Syntax: =IFERROR(value, value_if_error)
- 8. PMT:
-
Description: Calculates the monthly payment for a loan based on interest rate, loan amount, and term.
Syntax: =PMT(rate, nper, pv, [fv], [type])
- 9. SUMPRODUCT:
-
Description: Multiplies corresponding components in the given arrays and returns the sum of those products.
Syntax: =SUMPRODUCT(array1, [array2], …)
These are just a few examples of the advanced Excel formulas available for data analysis and manipulation. Excel provides a wide range of mathematical, statistical, and financial functions to assist in various tasks.