Generieke formule
=MAX(0,MIN(A1,1))
Samenvatting
Om een percentagewaarde te beperken zodat deze tussen 0% en 100% valt, kunt u een formule gebruiken die is gebaseerd op de MIN- en MAX-functies. In het getoonde voorbeeld is de formule in C5, naar beneden gekopieerd,:
=MAX(0,MIN(B5,1))
Het resultaat is dat negatieve waarden naar nul worden gedwongen, waarden boven 1 worden afgetopt op 1 en waarden tussen 0 en 1 worden niet beïnvloed.
Opmerking: alle waarden zijn opgemaakt met een percentage-getalnotatie.
Uitleg
Om dit probleem te begrijpen, moet u ervoor zorgen dat u begrijpt hoe het opmaken van percentages werkt. Kort samengevat zijn percentages decimale waarden: 0,1 is 10%, 0,2 is 20%, enzovoort. Het cijfer 1, als een percentage opgemaakt, is 100%. Meer over getalnotaties hier.
Het doel van dit voorbeeld is om inkomende percentagewaarden te beperken, zodat ze binnen een boven- en ondergrens vallen. Negatieve waarden en waarden boven 100% zijn niet toegestaan, dus het uiteindelijke resultaat moet een getal zijn tussen nul en 1 (0-100%), inclusief.
Hoewel de ALS-functie kan worden gebruikt om dit probleem op te lossen (zie hieronder), zal het resultaat wat langer en overbodig zijn. In plaats daarvan gebruikt het getoonde voorbeeld een combinatie van de MIN- en MAX-functies in een zeer compacte formule:
=MAX(0,MIN(B5,1))
Dit is een voorbeeld van nesten - de MIN-functie is genest in de MAX-functie. Nesten is een belangrijke bouwsteen voor meer geavanceerde formules.
Werkend van binnen naar buiten, wordt de MIN-functie gebruikt om inkomende waarden als volgt tot 1 te beperken:
MIN(B5,1) // get smaller value
Vertaling: retourneer de kleinste van B5 en 1. Voor elke waarde groter dan 1 wordt de waarde in B5 geretourneerd. In het voorbeeld bevat B5 -5% (-0,05), dus MIN retourneert -0,05. Dit resultaat wordt rechtstreeks teruggestuurd naar de MAX-functie:
=MAX(0,-0.05) // get larger value
Hier zien we de formule zijn werk doen. Omdat nul groter (groter) is dan -0,05, retourneert MAX nul als eindresultaat. De oorspronkelijke waarde wordt weggegooid.
ALS-functie
Zoals hierboven vermeld, kan de ALS-functie ook worden gebruikt om dit probleem op te lossen. Hiervoor hebben we twee afzonderlijke ALS-functies nodig. Eén IF dwingt negatieve waarden naar nul:
IF(B5<0,0,B5) // cap at zero
De tweede IF sluit grotere waarden af bij 1:
=IF(B5>1,1,B5) // cap at 1
Wanneer we de eerste IF in de tweede nesten, hebben we de uiteindelijke formule:
=IF(B5>1,1,IF(B5<0,0,B5))
Dit is een voorbeeld van een geneste IF. Het retourneert precies hetzelfde resultaat als de MIN- en MAX-formule hierboven, maar is iets complexer en overtolliger. Merk bijvoorbeeld op dat de verwijzing naar B5 drie verschillende keren voorkomt.
Waar het op neerkomt - wanneer u een keuze moet maken op basis van kleinere of grotere waarden, kunnen de MIN- en MAX-functies een slimme en elegante manier zijn om een formule eenvoudig te houden.
MEDIAAN-functie
OK, nu we het hebben gehad over nesten en het hebben gehad over de elegantie van MIN met MAX, moet ik vermelden dat het mogelijk is om dit probleem op te lossen zonder helemaal nesten met de MEDIAN-functie. De generieke versie van de formule ziet er als volgt uit:
=MEDIAN(0,1,A1)
Dit werkt omdat de functie MEDIAAN de mediaan (middelste getal) in een groep getallen retourneert. Als een waarde negatief is, wordt nul het middelste getal. Als een getal groter is dan 1, wordt 1 het middelste getal. Slim!
Merk echter op dat MEDIAAN alleen het middelste getal retourneert als het totale aantal waarden oneven is. Als het aantal waarden even is, retourneert MEDIAAN het gemiddelde van de twee getallen in het midden. Als gevolg hiervan, als de doelcel (A1) leeg is, retourneert MEDIAAN het gemiddelde van 1 en nul, wat 0,5 is, of 50% wanneer het is opgemaakt als een percentage.
