Generisk formel
(=MIN(IF((rng1"")*(rng2""),rng1-rng2)))
Sammendrag
For å beregne minimumsforskjellen mellom to sett med verdier, og ignorere tilfeller der hver verdi er tom, kan du bruke en matriseformel basert på MIN- og IF-funksjonene. I eksemplet vist er formelen i F4:
(=MIN(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
som returnerer 115, minimum av salgskostnad, og ignorerer tilfeller der hver verdi er tom.
Merk: dette er en matriseformel og må angis med Control + Shift + Enter.
Forklaring
I eksemplet som er vist er målet å beregne den minste forskjellen i salg minus kostnad, men bare når begge verdiene er angitt. Hvis en av verdiene er tomme, bør resultatet ignoreres. For å bekrefte at begge verdiene er tilgjengelige, er IF-funksjonen konfigurert til å bruke boolsk logikk med dette uttrykket:
(B5:B12"")*(C5:C12"")
Fordi hvert område inneholder 8 celler, er resultatet av denne operasjonen en matrise som dette:
(1;1;1;0;1;1;0;0)
Denne matrisen fungerer som et filter. I tilfeller der verdien er 1, tillater IF at verdiene går gjennom til MIN. De faktiske differanseverdiene beregnes med en annen arrayoperasjon:
B5:B12-C5:C12
som genererer dette resultatet:
(150;255;125;1100;150;115;-890;1025)
Etter at den logiske testen er evaluert, ser matrisen som sendes til MIN-funksjonen slik ut:
(150;255;125;FALSE;150;115;FALSE;FALSE)
Legg merke til at "differanseverdi" for rader der salg eller kostnad er blanke nå er FALSK. MIN-funksjonen ignorerer automatisk FALSE-verdier og returnerer minimum gjenværende tall, 115.
Maksimal forskjell ignorerer blanke
For å returnere maksimal forskjell uten å ignorere blanke verdier, kan du erstatte MAX med MIN:
(=MAX(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
Denne formelen fungerer på samme måte som forklart ovenfor.
Med MINIFS og hjelpekolonne
MINIFS-funksjonen kan brukes til å løse dette problemet, men det krever bruk av en hjelpekolonne med en formel som denne:
=B5-C5
Med formelen ovenfor i kolonne D kan MINIFS brukes slik:
=MINIFS(D5:D12,B5:B12,"",C5:C12,"")
Dette er ikke en matriseformel, og trenger ikke legges inn med kontroll + skift + enter.
