Formula generica
(=MIN(IF((rng1"")*(rng2""),rng1-rng2)))
Sommario
Per calcolare la differenza minima tra due serie di valori, ignorando i casi in cui uno dei valori è vuoto, è possibile utilizzare una formula di matrice basata sulle funzioni MIN e IF. Nell'esempio mostrato, la formula in F4 è:
(=MIN(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
che restituisce 115, il minimo del costo di vendita, ignorando i casi in cui uno dei valori è vuoto.
Nota: questa è una formula di matrice e deve essere inserita con Control + Maiusc + Invio.
Spiegazione
Nell'esempio mostrato, l'obiettivo è calcolare la differenza minima delle vendite meno il costo, ma solo quando sono stati immessi entrambi i valori. Se uno dei valori è vuoto, il risultato deve essere ignorato. Per confermare che entrambi i valori sono disponibili, la funzione IF è configurata per utilizzare la logica booleana con questa espressione:
(B5:B12"")*(C5:C12"")
Poiché ogni intervallo contiene 8 celle, il risultato di questa operazione è un array come questo:
(1;1;1;0;1;1;0;0)
Questo array funge da filtro. Nei casi in cui il valore è 1, IF consente ai valori di passare a MIN. I valori effettivi della differenza vengono calcolati con un'altra operazione di matrice:
B5:B12-C5:C12
che genera questo risultato:
(150;255;125;1100;150;115;-890;1025)
Dopo che il test logico è stato valutato, l'array passato alla funzione MIN ha il seguente aspetto:
(150;255;125;FALSE;150;115;FALSE;FALSE)
Notare che il "valore di differenza" per le righe in cui le vendite o il costo sono vuote è ora FALSE. La funzione MIN ignora automaticamente i valori FALSE e restituisce il minimo dei numeri rimanenti, 115.
Differenza massima ignorando gli spazi vuoti
Per restituire la differenza massima ignorando i valori vuoti, è possibile sostituire MAX per MIN:
(=MAX(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
Questa formula funziona allo stesso modo di quanto spiegato sopra.
Con MINIFS e colonna helper
La funzione MINIFS può essere utilizzata per risolvere questo problema, ma richiede l'uso di una colonna helper con una formula come questa:
=B5-C5
Con la formula sopra nella colonna D, MINIFS può essere utilizzato in questo modo:
=MINIFS(D5:D12,B5:B12,"",C5:C12,"")
Questa non è una formula di matrice e non è necessario immetterla con CTRL + MAIUSC + INVIO.
