Hoe kan de FREQUENCY() functie gebruikt kan worden? Deze post gaat over deze FREQUENCY() functie en komt van het MrExcel – forum. De techniek komt van twee heren en die krijgen dan ook de credits en alle lof namelijk Barry Houdini en Aladin Akyurek.

Het probleem

We hebben de prijzen van een aantal producten in kolom A en van drie winkels in kolommen B, D en F.

De producten hebben 2 categorieën namelijk Prijs en Aantal.  We gaan het prijs-gemiddelde uit de tabel halen. We weten dat het gemiddelde geen rekening houdt met 0-waarden in de cellen. Het juiste antwoord zou €24,48 moeten zijn waarbij de nullen genegeerd worden.

(14,99 9,89 34,79 42,49 13,99 34,09 18,65 43,19 13,49 7,89 17,99 42,29) / 12 = 24,48

In dit geval willen we een voorwaardelijk gemiddelde berekenen waarbij cellen met nul worden genegeerd. Normaal gesproken kunnen we dit doen met de AVERAGEIF()functie, maar dat is hier niet mogelijk omdat de Prijs-gegevens zich in drie afzonderlijke, niet-aaneengesloten, bereiken bevinden. Deze formule retourneert daarom een foutmelding:

=AVERAGEIF((B3:B7,D3:D7,F3:F7),”<>0″)

Om een ​​gemiddelde te berekenen van een niet-aaneengesloten verzameling bereiken kunnen we de werkbladfuncties SUM() en COUNT() gebruiken.

=SUM(B3:B7,D3:D7,F3:F7)/COUNT(B3:B7,D3:D7,F3:F7)

Echter de formule COUNT() telt ook de nullen waardoor de formule een resultaat van €19,58 retourneert, wat onjuist is. We kunnen proberen om de formule aan te passen om nullen te negeren door COUNTIF()in plaats van COUNT() te gebruiken, maar COUNTIF() kan ook niet worden gebruikt op niet-aaneengesloten bereiken, dus dit retourneert ook #VALUE!:

=SUM(B3:B7,D3:D7,F3:F7)/COUNTIF((B3:B7,D3:D7,F3:F7),”<>0″)

De vraag is dus hoe we het aantal niet-nulwaarden in deze cellen kunnen tellen en de nul-waarden negeren?

De oplossing

De oplossing is als volgt:
=SUM(B3:B7;D3:D7;F3:F7)/INDEX(FREQUENCY((B3:B7;D3:D7;F3:F7);0);2)
Formule invoeren met ENTER.

Dit SUM()deel van de formule is vrij eenvoudig.

FREQUENCY((B3:B7,D3:D7,F3:F7),0)

FREQUENCY() wordt hier gebruikt op niet-aaneengesloten bereiken.
De gegevens-matrix is (B3:B7,D3:D7,F3:F7). De bins_array is 0. FREQUENCY() retourneert een array met twee waarden: een telling van de prijzen met 0 die in de eerste bucket van 0 vallen, en een telling van de prijzen die groter zijn dan 0.

FREQUENCY((B3:B7,D3:D7,F3:F7),0)
wordt geëvalueerd tot {3;12}

We gaan er van uit dat er geen negatieve prijzen zijn. 3 is het aantal prijzen dat exact gelijk is aan 0 en 12 is het aantal prijzen dat groter is dan 0. 12 is het getal waarin we geïnteresseerd zijn. Vervolgens gebruiken we de INDEX()functie om dit uit de matrix te halen:

INDEX({3;12},2)
wordt geëvalueerd tot {12}

Vervolgens delen we de som van de waarden door 12 om het juiste antwoord te krijgen: €24,48.

SUM(B3:B7,D3:D7,F3:F7)/{7}
wordt geëvalueerd als: 392/{7}

resulteert in ons uiteindelijke antwoord: {€24,48} De formule werkt nog steeds, zelfs als er lege cellen zijn of als de niet-aaneengesloten bereiken een verschillende grootte hebben.