1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. 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.
8. 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.
9. 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.
Write Us- Support@virvijay.com.
