Generička formula
=MAX(0,MIN(A1,1))
Sažetak
Da biste ograničili postotnu vrijednost tako da padne između 0% i 100%, možete koristiti formulu koja se temelji na MIN i MAX funkcijama. U prikazanom primjeru, formula u C5, kopirana dolje, je:
=MAX(0,MIN(B5,1))
Rezultat je da su negativne vrijednosti prisiljene na nulu, vrijednosti preko 1 ograničene su na 1, a vrijednosti između 0 i 1 ne utječu.
Napomena: sve vrijednosti oblikovane u postotnom formatu broja.
Obrazloženje
Da biste razumjeli ovaj problem, morate razumjeti kako funkcionira formatiranje postotnog broja. Ukratko, postoci su decimalne vrijednosti: 0,1 je 10%, 0,2 je 20% itd. Broj 1, oblikovan kao postotak, iznosi 100%. Više o formatima brojeva ovdje.
Cilj ovog primjera je ograničiti ulazne postotne vrijednosti tako da spadaju u gornji i donji prag. Negativne vrijednosti i vrijednosti veće od 100% nisu dopuštene, pa konačni rezultat mora biti broj između nule i 1 (0-100%), uključujući.
Iako se za rješavanje ovog problema može koristiti funkcija IF (vidi dolje), rezultat će biti nešto duži i suvišan. Umjesto toga, prikazani primjer koristi kombinaciju MIN i MAX funkcija u vrlo kompaktnoj formuli:
=MAX(0,MIN(B5,1))
Ovo je primjer gniježđenja - funkcija MIN ugniježđena je unutar funkcije MAX. Gniježđenje je ključni gradivni element za naprednije formule.
Radeći iznutra prema van, funkcija MIN koristi se za ograničavanje dolaznih vrijednosti na 1 poput ove:
MIN(B5,1) // get smaller value
Prijevod: vratite manji od B5 i 1. Za bilo koju vrijednost veću od 1 vraća se vrijednost u B5. U primjeru, B5 sadrži -5% (-0,05), pa MIN vraća -0,05. Ovaj se rezultat vraća izravno u funkciju MAX:
=MAX(0,-0.05) // get larger value
Ovdje vidimo kako formula radi svoj posao. Budući da je nula veća (veća) od -0,05, MAX vraća nulu kao konačni rezultat. Izvorna vrijednost se odbacuje.
IF funkcija
Kao što je gore spomenuto, funkcija IF također se može koristiti za rješavanje ovog problema. Da bismo to učinili, trebaju nam dvije odvojene IF funkcije. Jedan IF prisiljava negativne vrijednosti na nulu:
IF(B5<0,0,B5) // cap at zero
Drugi IF ograničava veće vrijednosti na 1:
=IF(B5>1,1,B5) // cap at 1
Kada ugnijezdi prvi IF unutar drugog, imamo konačnu formulu:
=IF(B5>1,1,IF(B5<0,0,B5))
Ovo je primjer ugniježđenog IF-a. Vraća potpuno isti rezultat kao i gornja MIN i MAX formula, ali je nešto složeniji i suvišan. Primijetite, na primjer, referenca na B5 javlja se tri zasebna puta.
Dno - kada trebate odabrati na temelju manjih ili većih vrijednosti, funkcije MIN i MAX mogu biti pametan i elegantan način za pojednostavljivanje formule.
MEDIJAN funkcija
U redu, sad kad smo razgovarali o gniježđenju i razgovarali o eleganciji MIN-a s MAX-om, trebao bih spomenuti da je ovaj problem moguće riješiti bez ikakvog gniježđenja pomoću funkcije MEDIAN. Generička verzija formule izgleda ovako:
=MEDIAN(0,1,A1)
To djeluje jer funkcija MEDIAN vraća medijan (srednji broj) u grupi brojeva. Kad je vrijednost negativna, nula postaje srednji broj. Kad je broj veći od 1, 1 postaje srednji broj. Pametan!
Međutim, napomena MEDIAN vraća srednji broj samo kada je ukupan broj vrijednosti neparan. Ako je broj vrijednosti paran, MEDIAN vraća prosjek dva broja u sredini. Kao posljedica toga, ako je ciljana ćelija (A1) prazna, MEDIAN će vratiti prosjek 1 i nulu, što je 0,5 ili 50% kada se formatira kao postotak.
