Formula, Function of Excel
|
Category |
Formula |
Description |
|
Basic Formulas |
=SUM(A1:A10) |
Numbers ka sum calculate karega. |
|
=AVERAGE(A1:A10) |
Average value return karega. |
|
|
=MIN(A1:A10) |
Sabse chhoti value return karega. |
|
|
=MAX(A1:A10) |
Sabse badi value return karega. |
|
|
=COUNT(A1:A10) |
Sirf numeric values count karega. |
|
|
=COUNTA(A1:A10) |
Non-empty cells count karega. |
|
|
Conditional Counting & Summing |
=COUNTIF(A1:A10,
">50") |
A1:A10 range me jitne values 50 se
bade hain unko count karega. |
|
=SUMIF(A1:A10, ">50", B1:B10) |
A1:A10 me 50 se bade values wale corresponding B1:B10 ka
sum karega. |
|
|
Lookup & Reference |
=VLOOKUP(101, A2:C10, 2, FALSE) |
Column A me 101 dhundh kar
usi row ke 2nd column ka data return karega. |
|
=HLOOKUP(101, A1:J2, 2, FALSE) |
Row-wise lookup karega. |
|
|
=INDEX(A2:C10, MATCH(50, B2:B10,
0), 1) |
B column me 50 dhundh kar us row
ke A column ka data return karega. |
|
|
=INDEX(A2:A10, MATCH(MIN(B2:B10), B2:B10, 0)) |
B column me sabse chhoti value dhundh kar us row ke A
column ka data dega. |
|
|
=INDEX(A2:A10, MATCH(MAX(B2:B10),
B2:B10, 0)) |
B column me sabse badi value
dhundh kar us row ke A column ka data dega. |
|
|
Logical Formulas |
=IF(A1<40, "Fail", "Pass") |
Agar A1 ka value 40 se kam ho to "Fail", warna
"Pass". |
|
=IF(AND(A1<40, B1<40,
C1<40), "Fail", "Pass") |
Agar kisi bhi ek subject me marks
40 se kam ho, toh "Fail". |
|
|
Text Functions |
=LEFT(A1, 3) |
Left side ke 3 characters return karega. |
|
=RIGHT(A1, 3) |
Right side ke 3 characters return
karega. |
|
|
=MID(A1, 2, 4) |
2nd position se 4 characters return karega. |
|
|
=CONCAT(A1, " ", B1) |
Do cells ka data jodega. |
|
|
=TEXT(A1, "dd-mmm-yyyy") |
Date format change karega. |
|
|
Date & Time |
=TODAY() |
Aaj ki tareekh return karega. |
|
=NOW() |
Aaj ki date aur time return karega. |
|
|
=YEAR(A1) |
Date me se year nikalta hai. |
|
|
=MONTH(A1) |
Date me se month nikalta hai. |
|
|
=DAY(A1) |
Date me se day nikalta hai. |
|
|
Financial |
=PMT(5%/12, 60, -10000) |
Loan ki monthly payment calculate karega. |
|
=FV(5%/12, 60, -200) |
Future investment value batayega. |