Excel formulas
All the formula of excel sheet it help to use excel very easily
Arithmetic Formulas:
1. `=A1+B1` - Addition
2. `=A1-B1` - Subtraction
3. `=A1*B1` - Multiplication
4. `=A1/B1` - Division
5. `=A1^B1` - Exponentiation
6. `=A1%` - Percentage
7. `=ABS(A1)` - Absolute value
8. `=INT(A1)` - Integer part
9. `=MOD(A1,B1)` - Modulus (remainder)
10. `=ROUND(A1,B1)` - Rounding
11. `=TRUNC(A1,B1)` - Truncation
Statistical Formulas:
1. `=AVERAGE(A1:A10)` - Average
2. `=COUNT(A1:A10)` - Count
3. `=MAX(A1:A10)` - Maximum
4. `=MIN(A1:A10)` - Minimum
5. `=MEDIAN(A1:A10)` - Median
6. `=MODE(A1:A10)` - Mode
7. `=PERCENTILE(A1:A10,0.5)` - Percentile
8. `=RANK.A(A1,A1:A10)` - Rank
9. `=STDEV(A1:A10)` - Standard deviation
10. `=VAR(A1:A10)` - Variance
11. '=SUM(A1:H1)' - Total
Logical Formulas:
1. `=IF(A1>10,"PASS","FAIL")` - If statement
2. `=IFERROR(A1/B1,"Error")` - If error
3. `=IFBLANK(A1,"Blank")` - If blank
4. `=AND(A1>10,B1<5)` - And
5. `=OR(A1>10,B1<5)` - Or
6. `=NOT(A1>10)` - Not
7. `=XOR(A1>10,B1<5)` - Exclusive or
Text Formulas:
1. `=CONCATENATE(A1,B1)` - Concatenate
2. `=LEN(A1)` - Length
3. `=LOWER(A1)` - Lowercase
4. `=UPPER(A1)` - Uppercase
5. `=PROPER(A1)` - Proper case
6. `=TRIM(A1)` - Trim
7. `=REPLACE(A1,2,3,"new")` - Replace
8. `=FIND("a",A1)` - Find
9. `=SEARCH("a",A1)` - Search
10. `=MID(A1,2,3)` - Mid
Date and Time Formulas:
1. `=TODAY()` - Today's date
2. `=NOW()` - Current date and time
3. `=DATE(2022,1,1)` - Specific date
4. `=TIME(12,30,0)` - Specific time
5. `=DATEDIF(A1,B1,"D")` - Date difference
6. `=HOUR(A1)` - Hour
7. `=MINUTE(A1)` - Minute
8. `=SECOND(A1)` - Second
9. `=MONTH(A1)` - Month
10. `=YEAR(A1)` - Year
Financial Formulas :
1. `=PMT(0.05,60,10000)` - Loan payment
2. `=IPMT(0.05,60,10000,10)` - Interest payment
3. `=PPMT(0.05,60,10000,10)` - Principal payment
4. `=FV(0.05,60,10000)` - Future value
5. `=PV(0.05,60,10000)` - Present value
6. `=XNPV(0.05,A1:A10)` - Net present value
7. `=XIRR(A1:A10)` - Internal rate of return
8. `=RATE(60,10000,0.05)` - Interest rate
9. `=NPER(0.05,10000,0.05)` - Number of periods
10. `=ISPMT(0.05,60,10000)` - Interest portion of payment
Lookup and Reference Formulas
1. `=VLOOKUP(A2,B:C,2,FALSE)` - Vertical lookup
2. `=HLOOKUP(A2,B:C,2,FALSE)` - Horizontal lookup
3. `=INDEX(B:C,M
Advanced Excel formulas:
Logical and Conditional Formulas
1. "IF": `IF(logical_test, [value_if_true], [value_if_false])`
2. "IFS": `IFS(range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)`
3. "IFERROR": `IFERROR(cell, value_if_error)`
4. "IFBLANK": `IFBLANK(cell, value_if_blank)`
Text Formulas
1. "CONCATENATE": `CONCATENATE(text1, [text2], ...)`
2. "TEXTJOIN": `TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)`
3. "LEFT": `LEFT(text, num_chars)`
4. "RIGHT": `RIGHT(text, num_chars)`
5. "MID": `MID(text, start_num, num_chars)`
6. "FIND": `FIND(find_text, text, [start_num])`
7. "SEARCH": `SEARCH(find_text, text, [start_num])`
Date and Time Formulas
1. "TODAY": `TODAY()`
2. "NOW": `NOW()`
3. "DATEDIF": `DATEDIF(start_date, end_date, unit)`
4. "DATE": `DATE(year, month, day)`
5. "TIME": `TIME(hour, minute, second)`
6. "HOUR": `HOUR(time)`
7. "MINUTE": `MINUTE(time)`
8. "SECOND": `SECOND(time)`
Mathematical and Trigonometric Formulas
1. "SUM": `SUM(number1, [number2], ...)`
2. "SUMIF": `SUMIF(range, criteria, [sum_range])`
3. "SUMIFS": `SUMIFS(sum_range, range1, criteria1, [range2], [criteria2], ...)`
4. "AVERAGE": `AVERAGE(number1, [number2], ...)`
5. "AVERAGEIF": `AVERAGEIF(range, criteria, [average_range])`
6. "AVERAGEIFS": `AVERAGEIFS(average_range, range1, criteria1, [range2], [criteria2], ...)`
7. "SIN": `SIN(number)`
8. "COS": `COS(number)`
9. "TAN": `TAN(number)`
Statistical Formulas
1. "COUNT": `COUNT(value1, [value2], ...)`
2. "COUNTIF": `COUNTIF(range, criteria)`
3. "COUNTIFS": `COUNTIFS(range1, criteria1, [range2], [criteria2], ...)`
4. "MAX": `MAX(number1, [number2], ...)`
5. "MIN": `MIN(number1, [number2], ...)`
6. "STDEV": `STDEV(number1, [number2], ...)`
7. "STDEV.S": `STDEV.S(number1, [number2], ...)`
8. "VAR": `VAR(number1, [number2], ...)`
9. *VAR.S": `VAR.S(number1, [number2], ...)`
Lookup and Reference Formulas
1. "VLOOKUP": `VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`
2. "HLOOKUP": `HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])`
3. "INDEX/MATCH": `INDEX(range, MATCH(lookup_value, lookup_array, [match_type])`
4. "LOOKUP": `LOOKUP(lookup_value, lookup_vector, [result_vector])`
Array Formulas
1. "SUMPRODUCT": `SUMPRODUCT(array1, [array2], ...)`
2. "SUMIFS with multiple criteria": `SUMIFS(sum_range, range1, criteria1, range2,
criteria2, ...)`
3. "INDEX/MATCH with multiple criteria": `INDEX(range, MATCH(1, (criteria1) *
(criteria2), 0))`
Other Advanced Formulas
1. "XLOOKUP": `XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode])`
2. "FILTER": `FILTER(array, include, [if_empty])`
3. "RANDARRAY": `RANDARRAY(rows, columns, [min], [max], [seed])`
4. "SORT": `SORT(array, [column1], [order1], ...)`
5. "UNIQUE": `UNIQUE(array, [by_col], [exactly_once])`
Comments