=SUBTOTAAL of andere functie

[Withaar]

=SUBTOTAAL werk prima om bv de som van een gefilterde reeks op te tellen.

In zitten er in mijn geval ook 0 waarden tussen en bijbereken van het gemiddelde =SUBTOTAAL(101;"A1;A100") worden deze ook mee genomen, dit wil ik echter niet.
Ik ben op zoek naar het gemiddelde van de ingevulde waarden.
Op een ongefilterde lijst zou ik =SOM(A1:A100)-AANTAL.ALS(A1:A100;0) prima kunnen gebruiken, maar dat werkt niet op een gefilterde lijst...

Iemand een idee, mag ook in VBA zijn.

In het voorbeeld is D1 2,294, zonder de nul waarden zou dit '3' moeten zijn.

 

[Cobbe]

Helpt dit u vooruit?

=SUBTOTAAL(109;A1:A100)/SUBTOTAAL(103;B1:B100)

 

[Withaar]

Nee, helaas. 103 telt ook de 0 waarden.

"AANTALARG wordt gebruikt voor het tellen van het aantal niet-lege cellen en de waarden in de argumentenlijst"
en product kan je ook niet gebruiken want het als er één nul in zit is het resultaat nul...

Nb voor wie mee leest;

Syntaxis

SUBTOTAAL(functie_getal; verw1; verw2;...)

functie_getal is een getal van 1 tot 11 (inclusief verborgen waarden) of van 101 tot 111 (exclusief verborgen waarden) dat aangeeft welke functie moet worden gebruikt voor de subtotaalberekening in een lijst.

Functie_getal
(inclusief verborgen waarden) (exclusief verborgen waarden) Functie
1 101 GEMIDDELDE
2 102 AANTAL
3 103 AANTALARG
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SOM
10 110 VAR
11 111 VARP

 

[Cobbe]

Dit werkt ook als er tenminste geen versieconflict optreedt:

=SOMMEN.ALS(A1:A100;B1:B100;"A")/AANTALLEN.ALS(B1:B100;"A";A1:A100;">0")

 

[Withaar]

Perfect

 

[Withaar]

Toch niet helemaal, als de gefilterde data alleen nullen bevatte kreeg in een #Deel/0!
Opgelost met een =ALS(ISFOUT2(SOMMEN.ALS(A1:A100;B1:B100;"A")/AANTALLEN.ALS(B1:B100;"A";A1:A100;">0"));0;SOMMEN.ALS(A1:A100;B1:B100;"A")/AANTALLEN.ALS(B1:B100;"A";A1:A100;">0"))