Generieke formule
(=MIN(IF((rng1"")*(rng2""),rng1-rng2)))
Samenvatting
Als u het minimumverschil tussen twee sets waarden wilt berekenen, waarbij u gevallen negeert waarin een van beide waarden leeg is, kunt u een matrixformule gebruiken op basis van de MIN- en ALS-functies. In het getoonde voorbeeld is de formule in F4:
(=MIN(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
wat 115 teruggeeft, het minimum van verkoopkosten, waarbij gevallen waarin een van beide waarden leeg is, worden genegeerd.
Opmerking: dit is een matrixformule en moet worden ingevoerd met Control + Shift + Enter.
Uitleg
In het getoonde voorbeeld is het doel om het minimale verschil in omzet minus kosten te berekenen, maar alleen als beide waarden zijn ingevoerd. Als een van beide waarden leeg is, moet het resultaat worden genegeerd. Om te bevestigen dat beide waarden beschikbaar zijn, is de ALS-functie geconfigureerd om booleaanse logica te gebruiken met deze uitdrukking:
(B5:B12"")*(C5:C12"")
Omdat elk bereik 8 cellen bevat, is het resultaat van deze bewerking een array als deze:
(1;1;1;0;1;1;0;0)
Deze array fungeert als een filter. In gevallen waarin de waarde 1 is, laat IF waarden door naar MIN. De werkelijke verschilwaarden worden berekend met een andere arraybewerking:
B5:B12-C5:C12
wat dit resultaat genereert:
(150;255;125;1100;150;115;-890;1025)
Nadat de logische test is geëvalueerd, ziet de array die is doorgegeven aan de MIN-functie er als volgt uit:
(150;255;125;FALSE;150;115;FALSE;FALSE)
Merk op dat "verschilwaarde" voor rijen waarin Verkoop of Kosten leeg zijn nu FALSE is. De MIN-functie negeert automatisch FALSE-waarden en retourneert het minimum van de resterende getallen, 115.
Maximaal verschil dat lege plekken negeert
Als u het maximale verschil wilt retourneren zonder lege waarden te negeren, kunt u MAX vervangen door MIN:
(=MAX(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
Deze formule werkt op dezelfde manier als hierboven uitgelegd.
Met MINIFS en hulpkolom
De MINIFS-functie kan worden gebruikt om dit probleem op te lossen, maar het vereist het gebruik van een hulpkolom met een formule als deze:
=B5-C5
Met de bovenstaande formule in kolom D, kan MINIFS als volgt worden gebruikt:
=MINIFS(D5:D12,B5:B12,"",C5:C12,"")
Dit is geen matrixformule en hoeft niet te worden ingevoerd met control + shift + enter.
