Comprehensive Guide to Excel Functions: Date, Time, IF, Count, Lookup, and More
1. Date Functions
Starting Date in Excel:
Excel treats January 1, 1900, as day 1. Each subsequent day is assigned a sequential number.
=DATEVALUE("01/01/1900")→ Returns 1=DATEVALUE("02/01/1900")→ Returns 2
Two-Digit Year Entry:
- If the year is <30, Excel assumes 21st century (e.g., "25" → 2025).
- If the year is >=30, Excel assumes 20th century (e.g., "95" → 1995).
TODAY()
=TODAY()→ Returns the current date dynamically.
2. Time Functions
Time Representation in Excel
- Midnight = 0
- 6 AM = 0.25 (since it's 1/4th of a day)
- 12 PM = 0.5 (since it's half a day)
TIMEVALUE()
=TIMEVALUE("12:00:00")→ Returns 0.5 (noon).
NOW()
=NOW()→ Returns the current date and time.
3. Month & Year Functions
=MONTH(A1)→ Returns the month number (1-12).=YEAR(A1)→ Returns the year.
4. Conditional Functions
IF Statement
Syntax:
=IF(condition, value_if_true, value_if_false)
Example: =IF(A1>10, "High", "Low")
If A1 is greater than 10, it returns "High", otherwise "Low".
IFERROR()
-
=IFERROR(A1/B1, "Error!")
Returns "Error!" if B1 is zero.
AND & OR
=AND(A1>10, B1<5)→ Returns TRUE if both conditions are met.=OR(A1>10, B1<5)→ Returns TRUE if at least one condition is met.
5. Count Functions
COUNT()
Counts only numeric values.
=COUNT(A1:A10)
COUNTA()
Counts both numbers and text.
=COUNTA(A1:A10)
COUNTBLANK()
Counts empty cells.
=COUNTBLANK(A1:A10)
COUNTIF()
Counts values meeting a condition.
=COUNTIF(B4:B13, ">7")→ Counts numbers greater than 7.
COUNTIFS()
Counts values meeting multiple conditions.
=COUNTIFS(B4:B13, "A", C4:C13, ">100")
6. SUMIF & AVERAGEIF
SUMIF()
Adds values based on a condition.
=SUMIF(A1:A10, ">50", B1:B10)
SUMIFS()
Adds values based on multiple conditions.
=SUMIFS(B1:B10, A1:A10, ">50", C1:C10, "Pen")
AVERAGEIF()
Averages values based on a condition.
=AVERAGEIF(A1:A10, ">10", B1:B10)
AVERAGEIFS()
Averages values based on multiple conditions.
=AVERAGEIFS(B1:B10, A1:A10, ">10", C1:C10, "Mouse")
7. SUMPRODUCT
Multiplies corresponding values and sums them.
=SUMPRODUCT(A1:A5, B1:B5)
8. Data Validation
Used to restrict input.
- Data Tab → Data Validation
- Example: Allow only values between 1 and 100.
9. OFFSET Function
Returns a cell or range dynamically.
=OFFSET(A1,2,1)→ Returns value 2 rows down and 1 column right from A1.
10. Lookup Functions
VLOOKUP()
Searches for a value in the first column of a table.
=VLOOKUP(1001, A2:C10, 2, FALSE)
HLOOKUP()
Similar to VLOOKUP, but searches horizontally.
=HLOOKUP(1001, A1:J2, 2, FALSE)
LOOKUP()
Searches a sorted vector for an approximate match.
=LOOKUP(50, A1:A10, B1:B10)
11. MATCH & INDEX Functions
MATCH()
Finds the position of a value in a range.
=MATCH(50, A1:A10, 0)
INDEX()
Returns a value from a given row and column.
=INDEX(A1:C10, 2, 3)
12. Max, Min, Large, Small
MAX() & MIN()
=MAX(A1:A10)→ Returns highest value.=MIN(A1:A10)→ Returns lowest value.
LARGE() & SMALL()
=LARGE(A1:A10, 2)→ Returns the 2nd largest value.=SMALL(A1:A10, 3)→ Returns the 3rd smallest value.
ROW() & COLUMN()
=ROW(A1)→ Returns row number of A1.=COLUMN(B1)→ Returns column number of B1.
Summary of Key Exercises
How many times was machine "A" used?
=COUNTIF(A2:A200, "A")
Operations after 25-Dec-2004
=COUNTIF(B2:B200, ">"&DATE(2004,12,25))
Operations <4 minutes
=COUNTIF(C2:C200, "<4")
Total revenue for each salesperson
=SUMIF(A2:A200, "A", B2:B200)
Revenue for mouse transactions >20000 by B
=SUMIFS(C2:C200, A2:A200, "B", D2:D200, ">20000")
