Generisk formel
=MAX(0,MIN(A1,1))
Sammendrag
For å begrense en prosentverdi slik at den faller mellom 0% og 100%, kan du bruke en formel basert på MIN og MAX-funksjonene. I eksemplet vist er formelen i C5, kopiert ned,:
=MAX(0,MIN(B5,1))
Resultatet er at negative verdier blir tvunget til null, verdier over 1 er begrenset til 1, og verdier mellom 0 og 1 er upåvirket.
Merk: alle verdier formatert med prosentvis tallformat.
Forklaring
For å forstå dette problemet, sørg for at du forstår hvordan prosentformatering av tall fungerer. I et nøtteskall er prosentandeler desimalverdier: 0,1 er 10%, 0,2 er 20%, og så videre. Tallet 1, når det er formatert i prosent, er 100%. Mer om tallformater her.
Målet med dette eksemplet er å begrense innkommende prosentverdier slik at de faller innenfor en øvre og nedre terskel. Negative verdier og verdier over 100% er ikke tillatt, så det endelige resultatet må være et tall mellom null og 1 (0-100%), inkludert.
Selv om IF-funksjonen kan brukes til å løse dette problemet (se nedenfor), vil resultatet bli noe lengre og overflødig. I stedet bruker eksemplet som vises en kombinasjon av funksjonene MIN og MAX i en veldig kompakt formel:
=MAX(0,MIN(B5,1))
Dette er et eksempel på hekking - MIN-funksjonen er nestet inne i MAX-funksjonen. Nesting er en viktig byggestein for mer avanserte formler.
Arbeider fra innsiden og ut, MIN-funksjonen brukes til å sette innkommende verdier til 1 slik:
MIN(B5,1) // get smaller value
Oversettelse: returner den minste av B5 og 1. For en verdi over 1 returneres verdien i B5. I eksemplet inneholder B5 -5% (-0.05), så MIN returnerer -0.05. Dette resultatet returneres direkte til MAX-funksjonen:
=MAX(0,-0.05) // get larger value
Her ser vi formelen gjøre sitt. Fordi null er større (større) enn -0,05, returnerer MAX null som et endelig resultat. Den opprinnelige verdien kastes.
IF-funksjon
Som nevnt ovenfor kan IF-funksjonen også brukes til å løse dette problemet. For å gjøre dette trenger vi to separate IF-funksjoner. En IF tvinger negative verdier til null:
IF(B5<0,0,B5) // cap at zero
Den andre IF kapsler større verdier ved 1:
=IF(B5>1,1,B5) // cap at 1
Når vi hekker den første IF inne i den andre, har vi den endelige formelen:
=IF(B5>1,1,IF(B5<0,0,B5))
Dette er et eksempel på en nestet IF. Den gir nøyaktig det samme resultatet som MIN og MAX-formelen ovenfor, men er litt mer kompleks og overflødig. Legg merke til for eksempel at referansen til B5 forekommer tre separate ganger.
Poenget - når du trenger å ta et valg basert på mindre eller større verdier, kan funksjonene MIN og MAX være en smart og elegant måte å holde en formel enkel.
MEDIAN-funksjon
OK, nå som vi har snakket om hekking og snakket om elegansen til MIN med MAX, bør jeg nevne at det er mulig å løse dette problemet uten å hekke i det hele tatt med MEDIAN-funksjonen. Den generiske versjonen av formelen ser slik ut:
=MEDIAN(0,1,A1)
Dette fungerer fordi MEDIAN-funksjonen returnerer medianen (mellomtall) i en gruppe med tall. Når en verdi er negativ, blir null det midterste tallet. Når et tall er større enn 1, blir 1 det midterste tallet. Flink!
Merk imidlertid at MEDIAN bare returnerer det midterste tallet når det totale antallet verdier er merkelig. Hvis antall verdier er like, returnerer MEDIAN gjennomsnittet av de to tallene i midten. Som en konsekvens, hvis målcellen (A1) er tom, vil MEDIAN returnere gjennomsnittet av 1 og null, som er 0,5 eller 50% når den er formatert i prosent.
