Generisk formel
(=MIN(IF((rng1"")*(rng2""),rng1-rng2)))
Sammanfattning
För att beräkna minsta skillnad mellan två värden, om du ignorerar fall där något av värdena är tomma, kan du använda en matrisformel baserad på MIN- och IF-funktionerna. I exemplet som visas är formeln i F4:
(=MIN(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
som returnerar 115, lägsta försäljningskostnad, och ignorerar fall där något värde är tomt.
Obs: detta är en matrisformel och måste anges med Control + Shift + Enter.
Förklaring
I exemplet som visas är målet att beräkna minsta skillnad i försäljning minus kostnad, men bara när båda värdena har angetts. Om något av värdena är tomma bör resultatet ignoreras. För att bekräfta att båda värdena är tillgängliga är IF-funktionen konfigurerad att använda boolesk logik med detta uttryck:
(B5:B12"")*(C5:C12"")
Eftersom varje intervall innehåller åtta celler är resultatet av denna operation en matris så här:
(1;1;1;0;1;1;0;0)
Denna matris fungerar som ett filter. I fall där värdet är 1 tillåter IF att värden passerar till MIN. De faktiska skillnadsvärdena beräknas med en annan arrayoperation:
B5:B12-C5:C12
vilket genererar detta resultat:
(150;255;125;1100;150;115;-890;1025)
När det logiska testet har utvärderats ser matrisen som skickas till MIN-funktionen ut så här:
(150;255;125;FALSE;150;115;FALSE;FALSE)
Observera att "skillnadsvärde" för rader där antingen försäljning eller kostnad är tomt nu är FALSKT. MIN-funktionen ignorerar automatiskt FALSE-värden och returnerar det minsta antalet återstående siffror, 115.
Maximal skillnad vid ignorering av ämnen
För att returnera maximal skillnad utan att ignorera tomma värden kan du ersätta MAX med MIN:
(=MAX(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
Denna formel fungerar på samma sätt som förklaras ovan.
Med MINIFS och hjälparpelare
MINIFS-funktionen kan användas för att lösa detta problem, men det kräver användning av en hjälparpelare med en sådan formel:
=B5-C5
Med formeln ovan i kolumn D kan MINIFS användas så här:
=MINIFS(D5:D12,B5:B12,"",C5:C12,"")
Detta är inte en matrisformel och behöver inte anges med kontroll + skift + enter.
