Generisk formel
=MAX(0,MIN(A1,1))
Resumé
For at begrænse en procentværdi, så den falder mellem 0% og 100%, kan du bruge en formel baseret på MIN og MAX-funktionerne. I det viste eksempel er formlen i C5, kopieret ned,:
=MAX(0,MIN(B5,1))
Resultatet er, at negative værdier tvinges til nul, værdier over 1 er begrænset til 1, og værdier mellem 0 og 1 påvirkes ikke.
Bemærk: alle værdier formateret med procenttal.
Forklaring
For at forstå dette problem skal du sørge for at forstå, hvordan procentvis formatering af tal fungerer. I en nøddeskal er procenter decimalværdier: 0,1 er 10%, 0,2 er 20% osv. Nummeret 1, når det er formateret i procent, er 100%. Mere om nummerformater her.
Målet med dette eksempel er at begrænse indkommende procentværdier, så de falder inden for en øvre og nedre tærskel. Negative værdier og værdier over 100% er ikke tilladt, så det endelige resultat skal være et tal mellem nul og 1 (0-100%) inklusive.
Selvom IF-funktionen kan bruges til at løse dette problem (se nedenfor), vil resultatet være noget længere og overflødigt. I stedet for bruger det viste eksempel en kombination af MIN- og MAX-funktionerne i en meget kompakt formel:
=MAX(0,MIN(B5,1))
Dette er et eksempel på indlejring - MIN-funktionen er indlejret inde i MAX-funktionen. Indlejring er en vigtig byggesten for mere avancerede formler.
Arbejder indefra og ud, MIN-funktionen bruges til at sætte indgående værdier på 1 således:
MIN(B5,1) // get smaller value
Oversættelse: returner den mindste af B5 og 1. For en værdi over 1 returneres værdien i B5. I eksemplet indeholder B5 -5% (-0.05), så MIN returnerer -0.05. Dette resultat returneres direkte til MAX-funktionen:
=MAX(0,-0.05) // get larger value
Her ser vi formlen udføre sit arbejde. Da nul er større (større) end -0,05, returnerer MAX nul som et endeligt resultat. Den oprindelige værdi kasseres.
HVIS-funktion
Som nævnt ovenfor kan IF-funktionen også bruges til at løse dette problem. For at gøre dette har vi brug for to separate IF-funktioner. Én IF tvinger negative værdier til nul:
IF(B5<0,0,B5) // cap at zero
Den anden IF dækker større værdier ved 1:
=IF(B5>1,1,B5) // cap at 1
Når vi nester den første IF inden i den anden, har vi den endelige formel:
=IF(B5>1,1,IF(B5<0,0,B5))
Dette er et eksempel på en indlejret IF. Det returnerer nøjagtigt det samme resultat som MIN og MAX formlen ovenfor, men er lidt mere kompleks og overflødig. Bemærk, for eksempel, henvisningen til B5 forekommer tre separate gange.
Bundlinjen - når du skal træffe et valg baseret på mindre eller større værdier, kan MIN og MAX-funktionerne være en smart og elegant måde at holde en formel enkel.
MEDIAN-funktion
OK, nu hvor vi har talt om indlejring og talt om MIN's elegance med MAX, skal jeg nævne, at det er muligt at løse dette problem uden nogen indlejring overhovedet med MEDIAN-funktionen. Den generiske version af formlen ser sådan ud:
=MEDIAN(0,1,A1)
Dette fungerer, fordi MEDIAN-funktionen returnerer medianen (mellemtal) i en gruppe af tal. Når en værdi er negativ, bliver nul det midterste tal. Når et tal er større end 1, bliver 1 det midterste tal. Dygtig!
Bemærk dog MEDIAN returnerer kun det midterste tal, når det samlede antal værdier er ulige. Hvis antallet af værdier er jævnt, returnerer MEDIAN gennemsnittet af de to tal i midten. Som en konsekvens, hvis målcellen (A1) er tom, returnerer MEDIAN gennemsnittet på 1 og nul, hvilket er 0,5 eller 50%, når det er formateret i procent.
