Gjennomsnitt en ikke-sammenhengende Range

Tom trenger å gjennomsnitt en rekke ikke-sammenhengende celler, unntatt eventuelle celler som kan inneholde nullverdier. Spesielt må han gjennomsnitts cellene A1, C1, E1, G1, og J1, men bare telle disse cellene som ikke inneholder null.

Før du får inn hva som fungerer, la ta en titt på hva som ikke fungerer. Først av alt, det fungerer ikke å bare legge til cellene og dividere med 5; som ikke tar null verdier i betraktning. For det andre betyr det ikke fungerer å bruke ANTALL.HVIS i nevneren din formel, som vist her:

= (A1 + C1 + E1 + G1 + J1) / ANTALL.HVIS (A1: J1, "<> 0")

Dette fungerer ikke fordi den undersøker og teller celler innenfor hele spekteret av A1: J1, ikke bare de fem celler du vil vurderes i gjennomsnitt. Du kan også tenke at du kan velge de fem ikke-sammenhengende celler, gi dem et navn, og deretter bruke navnet i formelen. Mens Excel lar deg lage navnet, gir følgende feil:

= SUM (MyCells) / ANTALL.HVIS (MyCells, "<> 0")

Det ser ut til at ANTALL.HVIS vil bare fungere med et enkelt sammenhengende område, slik at ikke-sammenhengende natur MyCells utvalg kaster funksjon i en tailspin. Et lignende problem oppstår hvis du prøver å bruke en ikke-sammenhengende område med AVERAGEIF funksjon:

= GJENNOMSNITT (MyCells, "<> 0")

Siden du ikke kan bruke noen av funksjonene du kanskje vil bruke, har du igjen å stole på litt lengre formel for å beregne gjennomsnittlig. Du kan beregne gjennomsnittet av disse fem celler ved å bruke en bit av "lureri" til din evner, på denne måten:

= (A1 + + C1 E1 + G1 + J1) / ((A1 <> 0) + (C1 <> 0) + (E1 <> 0) + (G1 <> 0) + (J1 <> 0))

Evalueringen gjøres på hver celle i nevneren returnerer enten en 1 (for sann) eller et 0 (etter usann) avhengig av om cellen inneholder en annen verdi enn null eller ikke. Denne serie av verdier legges sammen, noe som gir den nødvendige telling av ikke-null-celler for nevneren.

Legg merke til at diskusjonen her har vært alt om nevneren i formelen, ikke teller. Grunnen er enkel-du kan legge alle fem verdier i telleren; null verdier der egentlig ingen rolle. Det eneste stedet de saken er i nevneren, som er det som gjør beregne dette gjennomsnittet så vanskelig.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (7845) gjelder for Microsoft Excel 2007, 2010, og 2013. Du kan finne en versjon av dette tipset for den eldre menygrensesnittet av Excel her: Midling en ikke-sammenhengende Range.