Excel Functions Guide
Text Functions
SUBSTITUTE: Replaces existing text with new text: =SUBSTITUTE(A1, "old", "new")
REPLACE: Replaces part of a text string: =REPLACE(A1, 9, 9, "Water")
Date Functions
DATEDIF: Calculates difference between two dates: =DATEDIF(A1, A2, "d")
Lookup Functions
VLOOKUP: =VLOOKUP("Coffee", I3:K37, 3, FALSE)
HLOOKUP: =HLOOKUP("Coffee", A1:Z3, 2, FALSE)
Math & Statistical
SUMIF: =SUMIF(H2:H100, "Coffee", G2:G100)
SUMIFS: =SUMIFS(G2:G100, H2:H100, "Coffee", F2:F100, ">50")
COUNTA: =COUNTA(A1:A10) - counts non-empty cells
COUNTIF: =COUNTIF(A1:A10, ">50")
COUNTIFS: =COUNTIFS(A1:A10, ">50", B1:B10, "<100")
RANK: =RANK(A2, A2:A10)
Logical Functions
IF: =IF(A1>10, "Yes", "No")
IFS: =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F")
SWITCH: =SWITCH(A1, 1, "One", 2, "Two", 3, "Three", "Other")
Volatile Functions
NOW: =NOW() - returns current date and time
RAND: =RAND() - returns random number between 0 and 1
RANDBETWEEN: =RANDBETWEEN(1, 100) - random number between 1 and 100
Financial Functions
PMT: =PMT(rate, nper, pv) - Loan payment
FV: =FV(rate, nper, pmt) - Future value
NPV: =NPV(rate, values) - Net present value
IRR: =IRR(values) - Internal rate of return
PV: =PV(rate, nper, pmt) - Present value
RATE: =RATE(nper, pmt, pv) - Interest rate
NPER: =NPER(rate, pmt, pv) - Number of periods