Generieke formule
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Samenvatting
Om waarden te tellen die buiten de tolerantie vallen in een set gegevens, kunt u een formule gebruiken die is gebaseerd op de SOMPRODUCT- en ABS-functies. In het getoonde voorbeeld is de formule in F6:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
waar "data" het benoemde bereik B5: B14 is, "target" het benoemde bereik F4 is en "tolerantie" het benoemde bereik F5.
Uitleg
Deze formule telt hoeveel waarden niet binnen een vaste tolerantie vallen. Hiermee wordt de variatie van elke waarde berekend:
ABS(data-target)
Omdat het benoemde bereik "data" 10 waarden bevat, zal het aftrekken van de doelwaarde in F4 een array met 10 resultaten creëren:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
De ABS-functie verandert eventuele negatieve waarden in positief:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Deze array wordt vergeleken met de vaste tolerantie in F5:
ABS(data-target)>tolerance
Het resultaat is een array of TRUE FALSE-waarden, en het dubbele negatief verandert deze in enen en nullen. Binnen SUMPRODUCT ziet de laatste array er als volgt uit:
(0;0;1;0;1;0;0;1;0;1)
waar nullen staan voor waarden binnen tolerantie, en 1s staan voor waarden buiten tolerantie. SUMPRODUCT telt vervolgens de items in de array op en retourneert het eindresultaat, 4.
Alle waarden binnen tolerantie
Om "Ja" te retourneren als alle waarden in een gegevensbereik binnen een bepaalde tolerantie vallen, en "Nee" als dat niet het geval is, kunt u de formule als volgt aanpassen:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Als SOMPRODUCT een getal groter dan nul retourneert, evalueert IF de logische test als WAAR. Een nulresultaat wordt geëvalueerd als FALSE.
Markeer waarden die buiten de tolerantie vallen
U kunt waarden buiten tolerantie markeren met een voorwaardelijke opmaakregel op basis van een formule als deze:
=ABS(B5-target)>tolerance
Deze pagina bevat meer voorbeelden van voorwaardelijke opmaak met formules.
