Simplying Complex Nested If Conditions In Excel With A Binomial Tree

Excel function tutorials

The other day, I got asked to help out with an excel problem that I initially thought would be very easy to figure out. Basically, here is an example:

Basically, I needed a formula that would determine what needs to be paid for each week knowing that the hourly rate increases over time. By itself, it might be very easy. For example, obviously the first week will be paid $10/hour so $100. For the second week, part would get paid at $10/hour and another at $12. How to determine how many hours to be paid at each quickly became difficult to work with. It was going to work but become very difficult to build. Why? For example, what happens if the worker does 90 hours in his first week? There are many different scenarios, just from those 3.

Using A Binomial Tree

Basically, I decided to take my problem and convert it into a binomial tree as you can see here:

This way, I can build my scenarios first, all of the “nested if conditions” without wondering about all of the calculations. As you will see, it makes things much easier. First, I will build the nested if condition by adding one condition at a time:

=IF(C2<25,1,2)
=IF(C2<25,IF(C3<25,1,3),2)
=IF(C2<25,IF(C3>25,IF(C3>50,1,2),3),2)
=IF(C2<25,IF(C3>25,IF(C3>50,1,2),3),IF(C2<50,4,6))
=IF(C2<25,IF(C3>25,IF(C3>50,1,2),3),IF(C2<50,IF(C3>50,4,5),6))

As you can see, this part alone can be confusing and difficult to build. Just imagine what it will be once we add the actual calcs! Now, I will replace numbers 1 through 6 by the calcs to get the correct amounts:

For example, the first scenario will be:

(25-C2)*$H$2+25*$H$3+(C3-50)*$H$4

While #2 is:

(25-C2)*$H$2+(C3-25)*$H$3)

#3 would be an easy one:

B3*$H$2

My end result is:

=IF(C2<25,IF(C3>25,IF(C3>50,(25-C2)*$H$2+25*$H$3+(C3-50)*$H$4,(25-C2)*$H$2+(C3-25)*$H$3),B3*$H$2),IF(C2<50,IF(C3>50,(50-$C$2)*H3+(C3-50)*$H$4,B3*$H$3),B3*$H$4))

As you can see, trying to do this from the start would have been difficult. You can also see the result here:

And download the spreadsheet.

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.