Excel Me Average Ka Formula? AVERAGE, AVERAGEIF & More

Formulas & functions · Updated November 22, 2026

Excel me average nikalne ka sabse simple formula hai =AVERAGE(range). Jaise agar aapke numbers A1 se A10 me hain, to =AVERAGE(A1:A10) type karein aur Enter dabayein — ye un numbers ka arithmetic mean de dega. Neeche hum is formula ke variations, common errors, aur practical examples ke saath detail me samjhenge.

Average Ka Matlab Kya Hota Hai

Average (ya mean) ek central value hota hai jo numbers ke group ko represent karta hai. Excel me AVERAGE function un sabhi numbers ka sum nikal kar unki count se divide kar deta hai. Example ke liye, agar aapke paas 10, 20, aur 30 hain, to average hoga (10+20+30)/3 = 20. Ye basic statistic har field me use hota hai — chahe wo sales report ho, student marks ho, ya monthly expenses. Excel me average nikalne ke kai tareeke hain, lekin AVERAGE sabse common aur direct hai.

AVERAGE Formula Kaise Kaam Karta Hai

AVERAGE function ka syntax hai: =AVERAGE(number1, [number2], ...). Aap numbers directly de sakte hain, ya cell references, ya ek range. Ye function text ya blank cells ko ignore karta hai, lekin logical values (TRUE/FALSE) ko ignore karta hai agar wo direct reference me hain, aur agar direct type ki gayi hain to count karta hai. Example: maan lijiye A1=5, A2=10, A3=blank, A4=text. Formula =AVERAGE(A1:A4) result dega 7.5 (kyunki sirf 5 aur 10 count hote hain). Agar aap chahte hain ki blank cells ko zero maana jaye, to aapko manually zero daalna hoga ya =AVERAGEIF use karna hoga.

Average Nikalne Ke Different Formulas

Excel me sirf ek average formula nahi hai — situation ke hisaab se alag functions use hote hain:

  • AVERAGE: Sabse basic, numbers ka mean deta hai. Example: =AVERAGE(B2:B100).
  • AVERAGEIF: Sirf un cells ka average jo ek condition satisfy karein. Example: agar A column me products hain aur B me sales, to formula =AVERAGEIF(A2:A20,"Pen",B2:B20) sirf “Pen” ki average sales dega.
  • AVERAGEIFS: Multiple conditions ke liye. Example: =AVERAGEIFS(B2:B20,A2:A20,"Pen",C2:C20,">10") — jahan C me quantity hai, ye un Pen entries ka average sales dega jinki quantity 10 se zyada hai.
  • MEDIAN: Middle value, jo outliers se effect nahi hoti. Example: =MEDIAN(A1:A10).
  • MODE: Sabse zyada baar aane wali value. Example: =MODE(A1:A10).

Har function ka apna use case hai. AVERAGEIFS tab use hota hai jab aapko ek se zyada condition lagani ho. MEDIAN tab jab data me extreme values hon jo average ko distort karein.

Average Ka Formula Kaise Choose Karein

Sahi formula chunne ke liye pehle ye samjhein ki aapka data kaisa hai aur aap kya poochna chahte hain. Agar aapko simple mean chahiye aur data me koi condition nahi hai, to =AVERAGE kaafi hai. Agar aapko kisi specific group ka average chahiye (jaise sirf “North” region ka), to =AVERAGEIF use karein. Agar multiple conditions hain (jaise region “North” aur sales > 1000), to =AVERAGEIFS use karein. Agar data me kuch bahut bade ya chhote numbers hain jo average ko jhukaa rahe hain, to =MEDIAN better rahega. Example: agar aapke paas 5 salaries hain: 20k, 25k, 30k, 35k, aur 200k, to AVERAGE 62k aayega jo misleading hai, lekin MEDIAN 30k sahi central value hai.

Common Errors Aur Unka Solution

Average formula me aksar ye errors aate hain:

  • #DIV/0!: Ye tab aata hai jab range me koi numeric value nahi hai. Example: =AVERAGE(A1:A5) agar sab cells blank hain to ye error aayega. Isko avoid karne ke liye aap =IFERROR(AVERAGE(A1:A5),0) use kar sakte hain.
  • #VALUE!: Ye tab aata hai jab aapne directly text ko argument me daala ho, jaise =AVERAGE("abc",10). Isko theek karne ke liye sirf numbers ya cell references use karein.
  • Blank cells ka issue: AVERAGE blank cells ko ignore karta hai, lekin agar aap chahte hain ki blanks ko zero maana jaye, to aapko unhe zero se replace karna hoga ya =SUM(range)/COUNT(range) use karna hoga, kyunki COUNT blank cells count nahi karta.
  • Hidden rows: AVERAGE hidden rows ko bhi include karta hai. Agar aap chahte hain ki sirf visible rows ka average ho, to aapko SUBTOTAL function use karna hoga: =SUBTOTAL(101,A1:A10) — yahan 101 average ke liye hai jo hidden rows ignore karta hai.

Average vs. Other Statistical Measures

Average (mean) ke alawa do aur common measures hain: median aur mode. Mean sabhi values ka sum divided by count hai. Median middle value hai jab data sorted ho. Mode sabse frequent value hai. Example: data set {1,2,2,3,100} ka mean 21.6 hai, median 2, aur mode 2. Mean outliers se bahut effect hota hai, isliye income ya property prices jaisi cheezon me median zyada use hota hai. Excel me ye sab functions available hain: AVERAGE, MEDIAN, aur MODE (ya MODE.SNGL). Aap chahein to ek hi formula me multiple statistics dekh sakte hain, jaise =AVERAGE(A1:A10) aur =MEDIAN(A1:A10) alag cells me.

Practical Examples With Real Data

Maan lijiye aapke paas ek classroom ke 5 students ke marks hain: A1=80, A2=85, A3=90, A4=75, A5=95. Average nikalne ke liye formula =AVERAGE(A1:A5) result dega 85. Ab agar aap sirf un students ka average chahte hain jinke marks 80 se zyada hain, to formula hoga =AVERAGEIF(A1:A5,">80") — ye 85, 90, aur 95 ka average dega, jo 90 hai. Isi tarah, agar aapke paas ek sales table hai jisme A column me product name hai aur B me sales, to aap =AVERAGEIF(A2:A20,"Laptop",B2:B20) se sirf Laptop ki average sales nikal sakte hain. Ye examples dikhate hain ki kaise aap data ke subsets par average laga sakte hain.

Frequently Asked Questions

Excel me average ka shortcut kya hai? Excel me average nikalne ka koi dedicated shortcut nahi hai, lekin aap AutoSum ka use kar sakte hain. Home tab par jaakar, Editing group me AutoSum ke dropdown se Average select karein. Ye automatically range select kar deta hai aur formula insert kar deta hai. Keyboard se aap Alt + = dabakar sum nikal sakte hain, lekin average ke liye manual formula likhna padta hai.

Average me blank cells ko kaise handle karein? AVERAGE function blank cells ko ignore karta hai, matlab wo unhe count nahi karta. Agar aap chahte hain ki blank cells ko zero maana jaye, to aapko unhe zero se replace karna hoga ya phir formula me unhe include karna hoga. Ek tarika hai =SUM(range)/COUNTA(range) use karna, lekin ye bhi blanks ko ignore karega. Sabse simple hai blanks ko 0 se replace kar dena.

AVERAGEIF aur AVERAGEIFS me kya antar hai? AVERAGEIF ek condition ke liye hota hai, jabki AVERAGEIFS multiple conditions ke liye. AVERAGEIFS ka syntax alag hai: pehle average range, phir condition ranges aur conditions. Example: =AVERAGEIFS(C2:C10,A2:A10,"North",B2:B10,">100") — yahan C average range hai, A aur B condition ranges hain. AVERAGEIF me pehle condition range aata hai, phir condition, phir average range.

Kya AVERAGE formula text cells ko ignore karta hai? Haan, AVERAGE function text cells ko ignore karta hai, chahe wo range me ho ya direct reference me. Lekin agar aap text ko direct argument me daalte hain, jaise =AVERAGE("abc",10), to ye #VALUE! error dega. Isliye hamesha cell references use karein.

Average nikalne ke liye Excel me kya shortcut hai Mac par? Mac par bhi AutoSum ka use hota hai, lekin shortcut alag hai. Windows par Alt + = hota hai, Mac par Command + Shift + T hota hai (ye sum ke liye hai, average ke liye nahi). Average ke liye aapko formula manually likhna hoga ya AutoSum menu se select karna hoga. Excel for Mac me Home tab par AutoSum ka dropdown hota hai.

Conclusion

Excel me average nikalna bahut simple hai — bas =AVERAGE use karein. Lekin jab aapke data me conditions hon ya outliers hon, to AVERAGEIF, AVERAGEIFS, ya MEDIAN jaise functions use karna behtar hota hai. In formulas ko practice karke aap apne data ka sahi average nikal sakte hain. Agar aapko kisi specific situation me help chahiye, to comment karein.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.