Excel Formulas Guide
1. Basic Formulas
- SUM: =SUM(A1:A5) -> Adds values from A1 to A5
- AVERAGE: =AVERAGE(B1:B5) -> Calculates the average
- COUNT: =COUNT(C1:C10) -> Counts numeric entries
- MAX: =MAX(D1:D10) -> Finds the highest number
- MIN: =MIN(E1:E10) -> Finds the lowest number
2. Logical Formulas
- IF: =IF(A1>10, "High", "Low") -> Returns "High" if A1>10, otherwise "Low"
- AND: =AND(A1>10, B1<5) -> TRUE if both conditions are true
- OR: =OR(A1>10, B1<5) -> TRUE if any condition is true
- NOT: =NOT(A1=10) -> TRUE if A1 is not 10
3. Text Formulas
- CONCATENATE: =CONCATENATE(A1, " ", B1) -> Joins two cells with space
- LEFT: =LEFT(A1, 3) -> Gets first 3 characters from A1
- RIGHT: =RIGHT(A1, 2) -> Gets last 2 characters from A1
- MID: =MID(A1, 2, 3) -> Extracts 3 characters from position 2
- LEN: =LEN(A1) -> Counts number of characters in A1
4. Date/Time Formulas
- TODAY: =TODAY() -> Shows current date
- NOW: =NOW() -> Shows current date and time
- DAY: =DAY(A1) -> Returns day from a date
- MONTH: =MONTH(A1) -> Returns month from a date
Excel Formulas Guide
- YEAR: =YEAR(A1) -> Returns year from a date
5. Lookup Formulas
- VLOOKUP: =VLOOKUP(101, A2:C10, 2, FALSE) -> Looks up value in first column
- HLOOKUP: =HLOOKUP(101, A1:Z3, 2, FALSE) -> Looks up value in first row
- INDEX: =INDEX(A2:C5, 2, 3) -> Gets value from 2nd row, 3rd column
- MATCH: =MATCH(50, A1:A10, 0) -> Finds position of 50 in the range
6. Math Functions
- ROUND: =ROUND(A1, 2) -> Rounds A1 to 2 decimal places
- CEILING: =CEILING(A1, 5) -> Rounds up to nearest 5
- FLOOR: =FLOOR(A1, 5) -> Rounds down to nearest 5