Excel Formulas with Explanation
Math & Trigonometry
SUM(A1:A10): Numbers ka total nikalta hai.
AVERAGE(A1:A10): Average calculate karta hai.
ROUND(12.345,2): Number ko given decimal places tak round karta hai (12.35).
ROUNDUP(12.3,0): Always upar round karega (13).
ROUNDDOWN(12.9,0): Always niche round karega (12).
SQRT(25): Square root deta hai (5).
POWER(2,3): Exponentiation deta hai (2^3=8).
PI(): Pi ki value deta hai (3.14159).
MOD(10,3): Remainder deta hai (1).
ABS(-15): Number ka absolute value (15).
RAND(): 0 se 1 ke beech random number.
RANDBETWEEN(1,100): Given range ke beech random number.
Text Functions
LEN("Hello"): Text ke characters count karta hai (5).
LEFT("Excel",2): Left se 2 character return karega (Ex).
RIGHT("Excel",2): Right se 2 character return karega (el).
MID("Excel",2,3): Beech ke characters return (xce).
TRIM(" Hello "): Extra spaces hata deta hai.
CONCATENATE("A","B"): Text join karta hai (AB).
CONCAT(A1:A3): Range ke text ko join karta hai.
TEXT(A1,"dd-mm-yyyy"): Date ko format karta hai.
UPPER("hello"): HELLO return karta hai.
LOWER("HELLO"): hello return karta hai.
PROPER("hello world"): Hello World (Proper case).
REPLACE("Excel",2,2,"123"): E123el banata hai.
SUBSTITUTE("12-12-2025","-","/"): 12/12/2025 banata hai.
SEARCH("x","Excel"): Text ki position (2), case-insensitive.
FIND("x","Excel"): Text ki position (2), case-sensitive.
Date & Time
TODAY(): Aaj ki date return karega.
NOW(): Aaj ki date + current time return karega.
DAY(TODAY()): Current day number.
MONTH(TODAY()): Current month number.
YEAR(TODAY()): Current year.
WEEKDAY(TODAY()): Weekday number (1=Sunday).
DATE(2025,10,1): Custom date create karega.
DATEDIF(A1,A2,"d"): 2 dates ke beech difference in days.
EDATE(TODAY(),3): Current date + 3 months.
EOMONTH(TODAY(),0): Current month ka last date.
Logical Functions
IF(A1>50,"Pass","Fail"): Condition check karke output deta hai.
AND(A1>10,B1<20): Dono condition true ho to TRUE.
OR(A1>10,B1<20): Agar ek bhi condition true ho to TRUE.
NOT(A1>10): True ko False aur False ko True banata hai.
IFERROR(A1/B1,"Error"): Error hone par default message show karega.
Lookup & Reference
VLOOKUP(101,A2:D10,2,FALSE): Vertical search karta hai aur value return karta hai.
HLOOKUP(101,A1:H5,3,FALSE): Horizontal search karta hai.
XLOOKUP(101,A2:A10,B2:B10,"Not Found"): Advanced lookup function.
INDEX(A1:C10,2,3): Row 2 aur column 3 ki value.
MATCH(50,A1:A10,0): Value ki position batata hai.
CHOOSE(2,"Red","Green","Blue"): 2nd option return karega (Green).
OFFSET(A1,2,3): A1 se 2 row niche aur 3 column aage ki cell.
Statistical Functions
MIN(A1:A10): Range ka smallest number.
MAX(A1:A10): Range ka largest number.
COUNT(A1:A10): Sirf numbers count karega.
COUNTA(A1:A10): Non-empty cells count karega.
COUNTIF(A1:A10,">50"): Condition ke hisaab se count.
COUNTIFS(A1:A10,">50",B1:B10,"Pass"): Multiple conditions count.
MEDIAN(A1:A10): Middle value of data.
MODE(A1:A10): Most repeated value.
STDEV(A1:A10): Standard deviation.
VAR(A1:A10): Variance.
Financial Functions
PMT(rate,nper,pv): Loan installment calculate karta hai.
FV(rate,nper,pmt): Future value calculate karta hai.
NPV(rate,values): Net Present Value calculate karta hai.
IRR(values): Internal Rate of Return calculate karta hai.
Information Functions
ISNUMBER(A1): TRUE agar cell mein number hai.
ISTEXT(A1): TRUE agar cell mein text hai.
ISERROR(A1): TRUE agar error hai.
ISBLANK(A1): TRUE agar cell khali hai.
TYPE(A1): Cell ka data type return karega.