Generička formula
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Sažetak
Za brojanje vrijednosti koje su izvan tolerancije u skupu podataka možete upotrijebiti formulu koja se temelji na funkcijama SUMPRODUCT i ABS. U prikazanom primjeru formula u F6 je:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
gdje je "podatak" imenovani raspon B5: B14, "cilj" imenovani raspon F4, a "tolerancija" imenovani raspon F5.
Obrazloženje
Ova formula broji koliko vrijednosti nisu u rasponu fiksne tolerancije. Varijacija svake vrijednosti izračunava se na sljedeći način:
ABS(data-target)
Budući da imenovani raspon "podaci" sadrži 10 vrijednosti, oduzimanjem ciljne vrijednosti u F4 stvorit će se niz s 10 rezultata:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
Funkcija ABS mijenja sve negativne vrijednosti u pozitivne:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Ovaj se niz uspoređuje s fiksnom tolerancijom u F5:
ABS(data-target)>tolerance
Rezultat je niz ili TRUE FALSE vrijednosti, a dvostruki negativ mijenja ih u jedinice i nule. Unutar SUMPRODUCT-a konačni niz izgleda ovako:
(0;0;1;0;1;0;0;1;0;1)
gdje nule predstavljaju vrijednosti unutar tolerancije, a 1s vrijednosti izvan tolerancije. SUMPRODUCT zatim zbraja stavke u polju i vraća konačni rezultat, 4.
Sve vrijednosti unutar tolerancije
Da biste vratili "Da" ako su sve vrijednosti u rasponu podataka unutar zadane tolerancije, a ako nisu, "Ne", formulu možete prilagoditi ovako:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Ako SUMPRODUCT vrati bilo koji broj veći od nule, IF će logički test procijeniti kao TRUE. Nulti rezultat će se procijeniti kao FALSE.
Istaknite vrijednosti iz tolerancije
Vrijednosti iz tolerancije možete istaknuti uvjetnim pravilom oblikovanja na temelju ovakve formule:
=ABS(B5-target)>tolerance
Na ovoj se stranici nalazi još primjera uvjetnog oblikovanja s formulama.
