AVERAGE dă o medie prea mică (include zerourile)
AVERAGE include în medie toate celulele cu 0, iar dacă lunile fără date au 0 în loc să fie goale, media scade. Exclude zerourile cu AVERAGEIF: =AVERAGEIF(B2:B9,"<>0") sau lasă celulele fără date goale, pe care AVERAGE le ignoră.
Explicat de Tudor Andrei Dumitrescu, Senior Software Developer, fondatorul Academiei ProgramatorilorActualizat la
Ce înseamnă
AVERAGE adună valorile și le împarte la câte numere sunt în interval. Celulele goale și textele sunt ignorate, dar 0 este un număr și intră în calcul. Dacă tabelul are 0 pentru lunile care nu au trecut încă (sau pentru zilele fără activitate), media e trasă în jos: 6000 de lei în 5 luni dau 1200 pe lună, dar împărțiți la 8 luni dau 750.
Nu e o eroare a lui Excel, ci o întrebare: 0 este o valoare reală (o lună fără vânzări) sau înseamnă „încă nu avem date”? Dacă e a doua variantă, folosește AVERAGEIF(interval,"<>0"), care ia în calcul doar valorile diferite de 0, sau lasă celulele goale.
Atenție și la opusul: dacă 0 este o valoare reală, a o exclude din medie înseamnă a umfla rezultatul.
De ce apare: cauzele frecvente
1Lunile viitoare au 0
=AVERAGE(B2:B9) (B7:B9 = 0)=AVERAGEIF(B2:B9,"<>0")AVERAGEIF calculează media doar pe valorile care respectă criteriul: aici, cele diferite de 0.
2Formula de calcul pune 0 în loc de gol
B7: =IFERROR(C7/D7,0)B7: =IFERROR(C7/D7,"")Un text gol este ignorat de AVERAGE, un 0 nu. Lasă formula să întoarcă "" când nu are ce calcula.
3Media fără valorile negative sau extreme
=AVERAGE(B2:B9)=AVERAGEIFS(B2:B9,B2:B9,">0",B2:B9,"<10000")Cu AVERAGEIFS poți exclude mai multe categorii de valori care nu trebuie să intre în medie.
Repară eroarea, direct în pagină
Tabelul are vânzările pe lunile ianuarie–august, dar iunie, iulie și august nu au trecut încă și au 0. Formula din E1, =AVERAGE(B2:B9), afișează 750, o medie prea mică. Repară formula din E1 ca media să se calculeze doar din lunile cu vânzări (fără zerouri), lăsând tabelul neschimbat.
Indiciu
Există o variantă a lui AVERAGE care primește un criteriu.
Reparația
Exclude zerourile cu AVERAGEIF:
=AVERAGEIF(B2:B9,"<>0")E1 afișează 1200.
De ce merge
AVERAGE împărțea totalul de 6000 la toate cele 8 celule cu numere, inclusiv cele trei zerouri, deci 750. AVERAGEIF cu criteriul "<>0" păstrează doar valorile diferite de 0: cele cinci luni cu vânzări. 6000 împărțit la 5 dă 1200. Pe măsură ce completezi lunile următoare, media le va include automat.
Alte forme ale mesajului
AVERAGE including zerosExcel average ignore zeroMedia include zerourileMEDIE prea mică
Cum o eviți pe viitor
- Lasă goale celulele pentru care nu ai încă date, în loc să scrii 0.
- Decide dinainte dacă 0 este o valoare reală sau un „lipsă date”.
- Folosește
AVERAGEIF(…,"<>0")când zerourile înseamnă lipsă. - Verifică media de mână pe un set mic: suma împărțită la câte valori reale ai.
Întrebări frecvente
AVERAGE ignoră celulele goale?
Da. Celulele goale și textele nu intră nici în sumă, nici în numărul de valori. Doar zerourile sunt luate în calcul.
De ce AVERAGEIF dă #DIV/0!?
Pentru că nicio celulă nu respectă criteriul, deci nu are ce împărți. Vezi pagina despre #DIV/0! în AVERAGE și AVERAGEIF.
Erori înrudite
#DIV/0! în AVERAGE și AVERAGEIF AVERAGE, AVERAGEIF și AVERAGEIFS dau #DIV/0! când nu au niciun număr de mediat: intervalul e gol sau niciun rând nu îndeplinește criteriul. Prinde cazul cu =IFERROR(AVERAGEIF(...),"Fără date"). COUNT returnează 0 pentru celule cu text (COUNT în loc de COUNTA) COUNT numără doar celulele cu numere (și date), așa că pe o coloană de nume sau de statusuri dă 0. Ca să numeri celulele completate, folosește COUNTA; pentru un anumit text, COUNTIF: =COUNTA(B2:B8) sau =COUNTIF(C2:C8,"Da"). Formula afișează 0 în loc de celulă goală (=B2 când B2 e goală) O referință la o celulă goală valorează 0, așa că =B2 afișează 0 când B2 e goală. Ca să rămână gol, verifică întâi celula: =IF(B2="","",B2). Pentru texte merge și =B2&"". SUMIF returnează 0: range și sum_range sunt inversate SUMIF are ordinea (range, criteria, sum_range): întâi coloana în care cauți criteriul, apoi criteriul, apoi coloana pe care o aduni. Dacă le inversezi, Excel caută „Nord” printre cifre, nu găsește nimic și dă 0: =SUMIF(A2:A7,"Nord",B2:B7). Învață Excel de la zero, cu exerciții la fiecare lecție
Curs Excel: lecții scurte, fiecare cu un exercițiu verificat automat, direct în browser. Primele 20 lecții sunt gratuite.
