I den här videon tittar vi på hur man bygger en formel som beräknar en 401k-matchning med flera kapslade IF-uttalanden.
I USA matchar många företag en anställds pensionsuppskjutning upp till en viss procent. I det här exemplet har matchen två nivåer.
I nivå 1 matchar företaget 100% upp till 4% av en anställds ersättning.
I nivå 2 matchar företaget 50% på uppskjutningar mellan 4% och 6%.
Så om en anställd bidrar med 10% matchar företaget 100% upp till 4% och 50% från 4 till 6%. Efter det finns det ingen match.
Låt oss titta på hur vi kan beräkna matchningen för dessa två nivåer med IF-uttalanden.
Sedan i nästa video tittar vi på hur vi helt enkelt kan använda formlerna.
För att beräkna matchningen för Tier 1 kan vi börja så här:
= IF (C5 <= 4%, C5 * B5)
Detta fungerar bra för uppskjutningar på 4% eller mindre, men vi får FALSE för allt över 4%.
Så vi måste utöka IF-funktionen för att hantera detta genom att lägga till ett värde om det är falskt. Eftersom nivå 1 är begränsad till 4%, och vi vet att uppskjutningen är minst 4%, använder vi helt enkelt 4%.
= IF (C5 <= 4%, C5 * B5,4% * B5)
När jag kopierar ner det här har vi rätt belopp för nivå 1.
För nivå 2 kan vi börja på samma sätt:
= IF (C5 <= 4%,
I det här fallet returnerar vi dock noll, om uppskjutningen är 4% eller mindre, eftersom det redan täcks av Tier 1.
= IF (C5 <= 4%, 0
För värdet om det är falskt är det lite mer knepigt.
Om vi har kommit så långt vet vi att uppskjutningen är större än 4%, och vi vet att matchningen är begränsad till 6% för nivå 2. Så vi behöver ytterligare en IF:
= IF (C5 <= 4%, 0, IF (C5 <= 6%, (C5-4%) * B5,2% * B5))
Om uppskjutningen är <= 6%, subtraherar du 4% och multiplicerar med B5. Om du är större än 6%, använd bara 2%, eftersom det är gränsen.
Sedan, eftersom matchningen är 50% i nivå 2, multiplicerar vi med 50%:
* 50%
När jag kopierar formeln har vi fullständiga nivå 2-belopp.
Så, för att sammanfatta …
Som du kan se kan denna typ av beräkningar bli ganska komplexa i Excel när vi lägger till fler IF-uttalanden för att hantera logiken.
I nästa video visar jag dig hur du förenklar dessa formler genom att ersätta IF-uttalandena med MIN-funktionen och lite boolesk logik.
