A. Mathematical and Arithmetic Formulas.
- =SUM(A1:A10) - Adds numbers in a range.
- =AVERAGE(A1:A10) - Calculates the average of a range.
- =PRODUCT(A1:A10) - Multiplies all numbers in a range.
- =SUBTOTAL(1, A1:A10) - Calculates a subtotal for a range.
- =MOD(A1, B1) - Returns the remainder of division.
- =ROUND(A1, 2) - Rounds a number to 2 decimal places.
- =CEILING(A1, 1) - Rounds a number up to the nearest multiple.
- =FLOOR(A1, 1) - Rounds a number down to the nearest multiple.
- =ABS(A1) - Returns the absolute value of a number.
B. Logical Formulas
- =IF(A1>10, "Yes", "No") - Checks if a condition is met.
- =AND(A1>10, B1<20) - Returns TRUE if all conditions are true.
- =OR(A1>10, B1<20) - Returns TRUE if any condition is true.
- =NOT(A1>10) - Reverses the logical value.
- =IFERROR(A1/B1, "Error") - Handles errors in calculations
C. Text Formulas
- =CONCAT(A1, B1) - Combines text from multiple cells.
- =LEFT(A1, 5) - Extracts the first 5 characters of a string.
- =RIGHT(A1, 5) - Extracts the last 5 characters of a string.
- =MID(A1, 2, 5) - Extracts 5 characters starting from the 2nd character.
- =LEN(A1) - Counts the number of characters in a string.
- =TRIM(A1) - Removes extra spaces from text.
- =UPPER(A1) - Converts text to uppercase.
- =LOWER(A1) - Converts text to lowercase.
- =TEXT(A1, "MM/DD/YYYY") - Formats a value as text.
D. Lookup and Reference Formulas
- =VLOOKUP(10, A1:B10, 2, FALSE) - Searches for a value vertically.
- =HLOOKUP(10, A1:Z10, 2, FALSE) - Searches for a value horizontally.
- =INDEX(A1:B10, 2, 1) - Returns a value at a specific position.
- =MATCH(10, A1:A10, 0) - Finds the position of a value in a range.
- =OFFSET(A1, 2, 3) - Returns a reference to a range offset from a given cell.
E. Date and Time Formulas
- =TODAY() - Returns the current date.
- =NOW() - Returns the current date and time.
- =DATE(2024, 12, 25) - Creates a specific date.
- =DAY(A1) - Extracts the day from a date.
- =MONTH(A1) - Extracts the month from a date.
- =YEAR(A1) - Extracts the year from a date.
- =HOUR(A1) - Extracts the hour from a time.
- =MINUTE(A1) - Extracts the minute from a time.
- =NETWORKDAYS(A1, B1) - Calculates working days between two dates.
F. Financial Formulas
- =PMT(5%/12, 60, -50000) - Calculates loan payments.
- =FV(5%/12, 60, -500, -50000) - Calculates the future value of an investment.
- =NPV(5%, A1:A10) - Calculates net present value.
- =IRR(A1:A10) - Calculates internal rate of return.
G. Statistical Formulas
- =MAX(A1:A10) - Finds the maximum value in a range.
- =MIN(A1:A10) - Finds the minimum value in a range.
- =COUNT(A1:A10) - Counts numeric values in a range.
- =COUNTA(A1:A10) - Counts non-empty cells.
- =COUNTIF(A1:A10, ">10") - Counts cells meeting a condition.
H. Array Formulas
- =TRANSPOSE(A1:A10) - Converts a row to a column or vice versa.
- =UNIQUE(A1:A10) - Returns unique values from a range.
- =SORT(A1:A10) - Sorts a range.
I. Error Checking Formulas
- =ISERROR(A1/B1) - Checks if a formula results in an error.
- =ISNUMBER(A1) - Checks if a cell contains a number.
- =ISBLANK(A1) - Checks if a cell is empty.
These formulas can significantly enhance your productivity and make complex tasks much easier. Start experimenting with these formulas in your day-to-day work and watch your efficiency. Do you have a specific formula or task in mind
