Урок на Excel: Пример за сложна формула 401k Match

В това видео ще разгледаме как да изградим формула, която изчислява 401k съвпадение, използвайки няколко вложени IF израза.

В САЩ много компании отговарят на отсрочка за пенсиониране на служители до определен процент. В този пример мачът има две нива.

В ниво 1 компанията отговаря на 100% до 4% от възнаграждението на служителя.

Във второ ниво, компанията отговаря на 50% при отсрочки между 4% и 6%.

Така че, ако служителят допринася с 10%, компанията отговаря на 100% до 4% и 50% от 4 до 6%. След това няма съвпадение.

Нека да разгледаме как можем да изчислим съвпадението за тези две нива с IF изрази.

След това в следващото видео ще разгледаме как можем просто да формулите.

За да изчислим съвпадението за ниво 1, можем да започнем така:

= IF (C5 <= 4%, C5 * B5)

Това работи добре за отсрочки от 4% или по-малко, но ще получим FALSE за всичко над 4%.

Затова трябва да разширим функцията IF, за да се справим с това, като добавим стойност, ако е false. Тъй като ниво 1 е ограничено на 4% и знаем, че отсрочването е най-малко 4%, ние просто използваме 4%.

= IF (C5 <= 4%, C5 * B5,4% * B5)

Когато копирам това, имаме правилните суми за ниво 1.

За ниво 2 можем да започнем по същия начин:

= IF (C5 <= 4%,

В този случай обаче, ако отлагането е 4% или по-малко, връщаме нула, тъй като това вече е покрито от ниво 1.

= IF (C5 <= 4%, 0

За стойността, ако е false, е малко по-сложно.

Ако сме стигнали дотук, знаем, че отлагането е по-голямо от 4% и знаем, че съвпадението е ограничено до 6% за ниво 2. Така че, ще ни трябва още IF:

= IF (C5 <= 4%, 0, IF (C5 <= 6%, (C5-4%) * B5,2% * B5))

Ако отсрочването е <= 6%, извадете 4% и умножете по B5. Ако е по-голямо от 6%, просто използвайте 2%, тъй като това е границата.

След това, тъй като съвпадението е 50% в ниво 2, умножаваме по 50%:

* 50%

Когато копирам формулата надолу, имаме пълни суми от второ ниво.

Така че, за да обобщим …

Както можете да видите, този вид изчисления могат да станат доста сложни в Excel, тъй като ние добавяме повече IF изрази за управление на логиката.

В следващото видео ще ви покажа как да опростите тези формули, като замените изразите IF с функцията MIN и малко булева логика.

Разбира се

Основна формула

Интересни статии...