Excel: Formula BasicsFree
Excel mein formulas banana aur step by step evaluate karna seekhein. Operators, brackets, cell references aur basic functions samjhein, taake formula copy hone par bhi us ka result sahi nikal sakein.
core level
Preparation level describes the foundations needed to study this topic. It is editorial guidance, not a difficulty score.
Cell ki value aur formula ka farq
Excel worksheet rows aur columns ki grid hai. A1 reference style mein column letter pehle aur row number baad mein aata hai: B3 column B, row 3 ka cell hai. Formula inputs ko use karke result nikalta hai. Cell mein formula enter karne ke liye = se shuru karein aur Enter dabayein.
Maan lein B2 mein numeric value 45 aur C2 mein numeric value 15 hai. D2 mein =B2+C2 type karein. D2 mein result 60 nazar aaye ga; D2 select karne par formula bar mein =B2+C2 mile ga. Formula aur displayed result ek cheez nahi. B2 ko 50 kar dein to automatic calculation enabled hone par result 65 ho ga; formula ab bhi wahi hai.
References input ki location batate hain. =45+15 mein dono values directly likhe hue constants hain; input cells change karne se is expression ke constants nahi badalte. =B2+C2 mein cell values use hoti hain. Yeh farq reusable spreadsheet calculation ki bunyaad hai. Microsoft formula overview.
=SUM(B2:B4)+5 mein SUM function, B2:B4 range reference, + operator aur 5 constant hai. Function built-in calculation ka naam hai; formula poora expression hai, jis mein function bhi ho sakta hai. Function ke parentheses mein inputs, ya arguments, aate hain. Is lesson ke examples standard English function names aur comma-separated arguments use karte hain; locale settings mein separator alag ho sakta hai. Inputs numbers, quoted text ya clearly stated blank cells hain; unstated text-to-number conversion assume nahi karte.
Calculation ko Excel ki notation mein likhna
Multiplication ke liye Excel mein * aur division ke liye / use hota hai. x type karna multiplication operator nahi.
| Operator | Meaning | Worked result |
|---|---|---|
+ | Addition | =8+5 gives 13 |
- | Subtraction | =8-5 gives 3 |
- before a value | Negation, yani sign badalna | =-8 gives -8 |
* | Multiplication | =8*5 gives 40 |
/ | Division | =8/4 gives 2 |
% | Percent: value ko 100 se divide karna | =25% gives numeric value 0.25 |
^ | Power | =2^4 gives 2×2×2×2 = 16 |
Power repeated multiplication ko short form mein likhta hai: 3^2 ka matlab 3×3 hai, 3×2 nahi. 25%*80 ka matlab 0.25×80 = 20. Agar amount par 25% increase chahiye to =80*(1+25%) se 100 milta hai; sirf =80*25% increase amount 20 deta hai, increased total nahi.
Negative input bhi number hai: =6*-3 mein result -18 hai. Operator ka minus sign aur number ke aage negation ka minus sign position se distinguish karein. Microsoft calculator examples.
Excel kis order mein evaluate karta hai?
Pehle parentheses ke andar ka expression evaluate hota hai; nested parentheses mein innermost group se shuru karein. Baqi operators apni precedence, yani priority, ke mutabiq lagte hain. Is unit mein used operators ki Excel order yeh hai:
| Higher se lower priority | Operators |
|---|---|
| Negation | - as in -3 |
| Percent | % |
| Power | ^ |
| Multiplication aur division | *, / |
| Addition aur subtraction | +, - |
| Text joining | & |
| Comparison | =, >, <, >=, <=, <> |
Colon range jaise reference operators ki apni higher precedence bhi hai; un ka kaam cells select karna hai. Range ko number ki tarah arithmetic digit na samjhein. Table ordinary operators ka relevant order dikhati hai. Microsoft operator order.
Same priority ho to left se right evaluate karein. * aur / barabar priority par hain; division har multiplication se pehle nahi hoti. Isi tarah + aur subtraction barabar priority par hain.
=48/6*3+2 ko dekhein:
- Division/multiplication addition se pehle hain.
- Same priority ki leftmost operation: 48/6 = 8.
- Phir 8×3 = 24.
- Aakhir mein 24+2 = 26.
=48/(6*3)+2 alag hai. Parentheses se 6×3 = 18 pehle hoga; 48/18+2 = 14/3, yani lagbhag 4.6667. Brackets sirf decoration nahi: woh grouping badalte hain.
Addition/subtraction ka example =30-8+2: 30-8 = 22, phir 22+2 = 24. =30-(8+2) mein bracket 10 hai, is liye result 20.
Negation ko generic maths rule se confuse na karein
Excel mein negation power se pehle hai. Is liye =-3^2 mein -3 ka square hota hai: (-3)×(-3) = 9. Agar positive 3 ka square nikal kar usay negative karna ho to =-(3^2) likhein: bracket ka result 9, phir negation se -9. Parentheses intended meaning clear karte hain; har calculator ya programming language ka rule Excel jaisa assume na karein.
Percent bhi power se pehle hai: =10%^2 mein 10% = 0.1, phir 0.1² = 0.01. =(10^2)% mein pehle 100, phir 100% = 1. Values same nazar aane ke bawajood grouping result badalti hai.
Supplied Past Paper ka =20*10/5*8 isi left-to-right rule ka application hai: 20×10 = 200, 200/5 = 40, 40×8 = 320. Result tak pohanchne ki wajah equal precedence hai, formula ko sirf ek yaad kiya hua answer na banayein.
Check yourself Excel mein =-4^2 aur =-(4^2) ke results kya hain?Try first, then view the answer
Pehla 16 hai, kyun ke negation power se pehle -4 banati hai. Doosra -16 hai, kyun ke parentheses mein 4 ka square pehle nikalta hai aur bahar minus baad mein lagta hai.
Comparison aur text joining
Calculation hamesha numeric total nahi deti. Comparison do values ka relation check karta hai aur TRUE ya FALSE return karta hai.
| Operator | Check |
|---|---|
= between values | Equal to |
<> | Not equal to |
> / < | Greater than / less than |
>= / <= | Greater/less than or equal to |
Formula ke start ka = formula marker hai. =B2=45 mein doosra = comparison hai. Agar B2 = 45 to result TRUE; B2 = 50 ho to FALSE. =8+2>=10 mein pehle addition se 10, phir comparison 10>=10 se TRUE milta hai. TRUE ko yahan total 10 kehna ghalat hai.
Concatenation & se text join karti hai. ="Gate"&" "&"Open" ka result Gate Open hai. Quotes text ki boundary hain; woh displayed result ka hissa nahi. Space automatically nahi aati, is liye beech mein quoted space di gayi. ="Gate"&"Open" se GateOpen banta hai. + addition aur & text joining ke liye hain; quoted numeric text ki automatic conversion is introductory example ka assumption nahi. Microsoft operator categories.
Range ko poora dekhein
Colon : do corner addresses ke darmiyan poori rectangular range, endpoints samet, batata hai. B2:C3 mein B2, C2, B3 aur C3 hain; sirf B2 aur C3 nahi.
| Row | Column B | Column C |
|---|---|---|
| 2 | 12 | 8 |
| 3 | 5 | 15 |
=SUM(B2:C3) mein 12+8+5+15 = 40. B2:B3 ek column mein 12 aur 5 ka range hai; us ka sum 17. Reference letter aur row ko ulta na parhein: C3 ki value 15 hai, B3 ki 5. Microsoft A1/range guidance.
Formula copy hone par references
Yahan copy/fill ki baat hai, Cut/Move ki nahi. Relative reference formula ke relative position ko follow karta hai. Formula ek row neeche copy ho to unlocked row bhi ek barhti hai; ek column right ho to unlocked column bhi ek right jata hai. $ us ke baad aane wale row ya column ko lock karta hai.
| Reference | Kya locked hai? | Formula one column right, one row down copy ho to |
|---|---|---|
A2 | Kuch nahi | B3 |
$A$2 | Column aur row | $A$2 |
$A2 | Sirf column A | $A3 |
A$2 | Sirf row 2 | B$2 |
Dollar sign currency calculation ka operator yahan nahi; reference lock hai. Relative, absolute aur mixed reference alag copying behaviour rakhte hain. Constant 5 copy hone par 6 nahi banta: yeh cell address nahi.
Maan lein A2 = 120, A3 = 200 aur B1 = 10% tax rate hai. C2 mein =A2*$B$1 se tax 12. Formula C3 mein copy ho to =A3*$B$1: item amount next row se 200, same tax rate se result 20. Rate ko B1 unlocked chhor dete to neeche copy par B2 ban jata, jo is requirement ka fixed rate nahi.
Mixed-reference example: C2 mein =$A2*B$1 hai. D3 tak copy ka offset one column right aur one row down hai. $A2 ka column fixed, row 3 ho ga; B$1 ka row fixed, column C ho ga. Naya formula =$A3*C$1 hai. A3 = 7 aur C1 = 4 ho to result 28. Dono locks ko alag check karna ghalat reference pakarne ka useful tareeqa hai. Microsoft copied-reference guidance.
Check yourself =D5*$A$1 ko one row down copy karein. Kaunsa formula banega?Try first, then view the answer
=D6*$A$1. D5 relative hai, is liye row badli; $A$1 ka column aur row dono locked hain.
Basic functions se numeric range ka result
SUM values add karta hai. AVERAGE numeric values ka total un ki count se divide karta hai. COUNT numeric entries ki count, MIN smallest aur MAX largest number deta hai. Yeh function names different goals ke liye hain; COUNT ko total amount ka naam na samjhein.
Is range mein numbers actual numeric cells hain, aur B4 sach mein blank hai:
| Cell | Value |
|---|---|
| B2 | 8 |
| B3 | 0 |
| B4 | blank |
| B5 | 16 |
| Formula | Result aur reasoning |
|---|---|
=SUM(B2:B5) | 24: 8+0+16 |
=COUNT(B2:B5) | 3: zero bhi numeric entry hai; blank nahi |
=AVERAGE(B2:B5) | 8: 24/3; blank ki wajah se 4 se divide nahi hota |
=MIN(B2:B5) | 0: zero actual value hai |
=MAX(B2:B5) | 16: largest numeric value |
Blank aur zero alag hain. Agar B4 mein 0 enter kar dein to COUNT 4 aur AVERAGE 24/4 = 6 ho jaye ga; SUM ab bhi 24 hai. B4 ke blank ko missing value samajhna aur entered zero ko actual observation samajhna spreadsheet reasoning mein zaroori hai. Range mein plain text ho to COUNT us text cell ko numeric entry nahi ginta; yahan advanced direct-argument conversion rules discuss nahi kiye gaye.
Function formula ke andar bhi aa sakta hai: =SUM(B2:B5)+6 gives 30, lekin poora formula SUM function ka sirf naam nahi. AutoSum ordinary sums banane mein madad karta hai; proposed range ko phir bhi check karein. Microsoft numeric counting, AVERAGE aur blank/zero ka farq, basic aggregate functions, MIN/MAX.
Error aaye to cause sahi karein
Formula ka font ya colour change karne se broken calculation fix nahi hoti. Basic errors ko input aur expression ke saath relate karein:
| Error | Simple cause | Cause ko address karna |
|---|---|---|
#DIV/0! | Number ko zero ya blank denominator se divide kiya | Denominator/reference check karein; valid nonzero value use karein jab available ho |
#NAME? | Excel formula ka text/name nahi pehchanta, jaise =SMU(B2:B5) | Intended function spelling ko SUM karein |
#REF! | Invalid cell reference, jaise referenced cell delete hone ke baad broken formula | Correct existing reference se formula rebuild karein, ya applicable ho to deletion Undo karein |
Example: D2 mein =B2/C2, B2 = 18 aur C2 = 0. Result #DIV/0! hai. Agar actual divisor 3 tha to correct input C2 = 3 se result 6. Sirf error hide karna original missing/wrong input ka factual correction nahi; zero ka imaginary replacement choose na karein. Microsoft formula error causes.
Kisi nayi formula question ko solve karte waqt pehle input values/types, phir ranges aur references, phir grouping/precedence aur required result type dekhein. Copying ho to offset aur $ locks evaluate karne se pehle apply karein. Is unit mein advanced lookup/nested functions, arrays, external references, iterative calculations aur data-analysis tools shamil nahi. Microsoft sources 3 October 2026 ko dekhe gaye.