In deze video bekijken we hoe je een formule kunt bouwen die een 401k-overeenkomst berekent met behulp van verschillende geneste IF-instructies.
In de VS evenaren veel bedrijven het uitstel van pensioen van werknemers tot een bepaald percentage. In dit voorbeeld heeft de wedstrijd twee niveaus.
In niveau 1 komt het bedrijf overeen met 100% tot 4% van de beloning van een werknemer.
In tier 2 komt het bedrijf overeen met 50% op uitstel tussen 4% en 6%.
Dus als een werknemer 10% bijdraagt, komt het bedrijf overeen met 100% tot 4% en 50% van 4 tot 6%. Daarna is er geen match.
Laten we eens kijken hoe we de overeenkomst voor deze twee lagen kunnen berekenen met IF-statements.
Vervolgens zullen we in de volgende video bekijken hoe we de formules eenvoudig kunnen gebruiken.
Om de match voor Tier 1 te berekenen, kunnen we als volgt beginnen:
= ALS (C5 <= 4%, C5 * B5)
Dit werkt prima voor uitstel van 4% of minder, maar we krijgen FALSE voor iets meer dan 4%.
We moeten dus de IF-functie uitbreiden om dit af te handelen door een waarde toe te voegen als false. Aangezien tier 1 is afgetopt op 4%, en we weten dat het uitstel ten minste 4% is, gebruiken we gewoon 4%.
= ALS (C5 <= 4%, C5 * B5,4% * B5)
Als ik dit kopieer, hebben we de juiste bedragen voor Tier 1.
Voor Tier 2 kunnen we op dezelfde manier beginnen:
= ALS (C5 <= 4%,
In dit geval echter, als het uitstel 4% of minder is, retourneren we nul, aangezien dat al wordt gedekt door Tier 1.
= ALS (C5 <= 4%, 0
Voor de waarde als false, is het een beetje lastiger.
Als we zover zijn gekomen, weten we dat het uitstel groter is dan 4%, en we weten dat de match op 6% is gelimiteerd voor niveau 2. We hebben dus nog een IF nodig:
= ALS (C5 <= 4%, 0, ALS (C5 <= 6%, (C5-4%) * B5,2% * B5))
Als het uitstel <= 6% is, trekt u 4% af en vermenigvuldigt u met B5. Als het groter is dan 6%, gebruik dan gewoon 2%, want dat is de limiet.
Dan, omdat de overeenkomst 50% is in niveau 2, vermenigvuldigen we met 50%:
* 50%
Als ik de formule kopieer, hebben we volledige bedragen van niveau 2.
Dus, om samen te vatten …
Zoals u kunt zien, kunnen dit soort berekeningen behoorlijk complex worden in Excel naarmate we meer IF-instructies toevoegen om de logica te beheren.
In de volgende video laat ik je zien hoe je deze formules kunt vereenvoudigen door de IF-instructies te vervangen door de MIN-functie en een beetje booleaanse logica.
