Generisk formel
=MAX(0,MIN(A1,1))
Sammanfattning
För att begränsa ett procentvärde så att det faller mellan 0% och 100% kan du använda en formel baserad på funktionerna MIN och MAX. I exemplet som visas är formeln i C5, kopierad ned:
=MAX(0,MIN(B5,1))
Resultatet är att negativa värden tvingas till noll, värden över 1 begränsas till 1 och värden mellan 0 och 1 påverkas inte.
Obs: alla värden formaterade med procenttal.
Förklaring
För att förstå detta problem, se till att du förstår hur procentuell formatering av tal fungerar. I ett nötskal är procenttal decimalvärden: 0,1 är 10%, 0,2 är 20% och så vidare. Siffran 1, när den är formaterad i procent, är 100%. Mer om nummerformat här.
Målet med detta exempel är att begränsa inkommande procentvärden så att de faller inom en övre och nedre tröskel. Negativa värden och värden över 100% är inte tillåtna, så slutresultatet måste vara ett tal mellan noll och 1 (0-100%) inklusive.
Även om funktionen IF kan användas för att lösa detta problem (se nedan) blir resultatet något längre och överflödigt. Istället använder exemplet som visas en kombination av MIN- och MAX-funktionerna i en mycket kompakt formel:
=MAX(0,MIN(B5,1))
Detta är ett exempel på kapsling - MIN-funktionen kapslas inuti MAX-funktionen. Häckande är en viktig byggsten för mer avancerade formler.
Arbetar inifrån och ut, MIN-funktionen används för att begränsa inkommande värden till 1 så här:
MIN(B5,1) // get smaller value
Översättning: returnera det minsta av B5 och 1. För ett värde över 1 returneras värdet i B5. I exemplet innehåller B5 -5% (-0.05), så MIN returnerar -0.05. Detta resultat returneras direkt till MAX-funktionen:
=MAX(0,-0.05) // get larger value
Här ser vi att formeln gör sitt arbete. Eftersom noll är större (större) än -0,05 returnerar MAX noll som slutresultat. Det ursprungliga värdet kasseras.
IF-funktion
Som nämnts ovan kan IF-funktionen också användas för att lösa detta problem. För att göra detta behöver vi två separata IF-funktioner. En IF tvingar negativa värden till noll:
IF(B5<0,0,B5) // cap at zero
Den andra IF täcker större värden vid 1:
=IF(B5>1,1,B5) // cap at 1
När vi hyser den första IF inuti den andra har vi den slutliga formeln:
=IF(B5>1,1,IF(B5<0,0,B5))
Detta är ett exempel på en kapslad IF. Det returnerar exakt samma resultat som MIN- och MAX-formeln ovan, men är något mer komplex och överflödig. Observera till exempel att hänvisningen till B5 sker tre separata tider.
Slutsatsen - när du behöver göra ett val baserat på mindre eller större värden kan MIN och MAX-funktionerna vara ett smart och elegant sätt att hålla en formel enkel.
MEDIAN-funktion
OK, nu när vi har pratat om häckning och pratat om MINs elegans med MAX, bör jag nämna att det är möjligt att lösa detta problem utan att göra någon häckning alls med MEDIAN-funktionen. Den generiska versionen av formeln ser ut så här:
=MEDIAN(0,1,A1)
Detta fungerar eftersom MEDIAN-funktionen returnerar median (mittnummer) i en grupp av siffror. När ett värde är negativt blir noll mitten. När ett tal är större än 1 blir 1 det mellersta numret. Duktig!
Observera MEDIAN returnerar emellertid bara mitten när det totala antalet värden är udda. Om antalet värden är jämnt returnerar MEDIAN medelvärdet av de två siffrorna i mitten. Som en konsekvens, om målcellen (A1) är tom, kommer MEDIAN att returnera genomsnittet av 1 och noll, vilket är 0,5 eller 50% när det formateras i procent.
