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.

I got an interesting email the other day from a reader who was looking to add Boolean logic to spreadsheets. First, you might wonder what Boolean is:

-boolean is a data type with only two possible values: true or false – in excel the two possible values are 0 (false) or 1 (true)

How Boolean Logic Can Be Used In Excel

In some cases, you might want to use it instead of “if” conditions. The example he gave me was:

=(8>3)*(1000/10) which will give 10

Basically, in this case, 8>3 is a Boolean data which is “true” so it equals 1. You’d then end up with a result of 10.

Here is an example. Suppose that you have a sales staff that makes a commission of 5% on all sales on one condition. They must make at least $3,000 worth of sales in that week to make a commission. If they don’t, the commission is $0.

As you can see, this is a perfect case for Boolean. What I’ll do is:

=(D3>3000)*D3*0.05

See the end result here:

There are many different possibilities but as you can see, this makes it very easy to understand and play with. The key is to require a “numeric value” that can be multiplied

Have you ever used or are you considering using Boolean based formulas in excel?

Greetings everyone! Today, I received a question from a recent buyer of our Excel ebook: “Taking your Excel skills to the next level“. Basically, the reader was asking for more information about a specific function. Here is the question:

I would like additional explanations of the actual inputs in the formulas. If you have the formula rank(A3:C:C), what do the inputs mean or what do you put in there as I have no idea?

Very fair question. I will without a doubt be working on improving the ebook to make that more clear. I did however want to get into that question as a general because this can be very useful no matter what function you are using. Let’s use that “rank” function and use a very simple example:

If you know which function you’d like to try or have some kind of clue, you can simply start typing that function in the appropriate cell and as you type, the name of the available functions will narrow down:

You can either start typing the info or in the much more likely situation where you’re not sure how to use the fields, you can click on the top “F(x)” at the top of this screenshot:

You will then see a screen where you can get information about each field:

In this case, the number, I’m trying to “rank” is in C1, so I’ll write down and select the next field:

Ref is the range where the numbers I’d like to be ranked are so in this case it would be: C1:C5

The third field is not in bold and as you can see in the description, is not required!

Another alternative is also to click on the bottom left in the “help on this function” which gives a description and example of the function in action, see here:

I’m always more than happy to help and will certainly try to be careful but I thought this post might help of a few people:)

In some cases, I’m looking to find the exact value that would get me to a specific result. Here is one sample. Look at this sample spreadsheet. Suppose that my company expects to have sales of $1,000,000 this year. I’m trying to set targets for myself in order to reach $3,000,000 by 2021, 10 years from now. I will simply set up the spreadsheet with a bogus growth rate as you can see here:

The formula that I use is the following:

=D5*(1+$G$4)

Which I dragged. As you can see, with a growth rate of 1% annually, I would have sales of $1,093,685 in 2021 which is way under my target. How much growth do I need to reach my target? I could simply try changing the growth value until I get close to $3M but there exists a much faster method in excel. I will use the goal-seek function that you can see in the data menu:

You can see the result here:

So 12.98% is the annual growth rate required to reach my objective. As you can imagine, this function can be very useful even though it has some limitations.

Lately I’ve been getting a few different questions that are awfully similar but I remain unsure of the best title to use or the best way to explain the problem. I’ll get started right away by giving you a few examples that will show you. Hopefully someone can suggest a better title for this post:)

First off, a reader was trying to prepare a spreadsheet that would calculate an amount to bill based on the number of calls using the following grid:

My problem is – Calculate amount per connection as
Rs 0 for first 500 Calls
Rs. 1 for Next 300 Calls
Rs. 1.5 for Next 400 Calls &
rs 1.75 rest of calls

See the following chart for details:

How would you resolve this? There are obviously many different ways but this is a good example of a situation where using nested if conditions can work out very well. First off, I’ll translate what I’m being told so:

My problem is – Calculate amount per connection as
Rs 0 for first 500 Calls
Rs. 1 for Next 300 Calls
Rs. 1.5 for Next 400 Calls &
rs 1.75 rest of calls

Becomes:

My problem is – Calculate amount per connection as
Rs 0 for first 500 Calls
Rs. 0 + (x-500)*1 for next 300 calls
Rs. 300 + (x-800)*1.5 for next 400 calls
rs 900 + (x-1200)*1.75 for rest of calls

This makes it much easier to build a nested if condition. I can simply build it one condition at a time such as:

=IF(D2<500,0,1) =IF(D2<500,0,IF(D2<800)) =IF(D2<500,0,IF(D2<800,(D2-500)*1))) =IF(D2<500,0,IF(D2<800,(D2-500)*1,IF(D2<1200)))) =IF(D2<500,0,IF(D2<800,(D2-500)*1,IF(D2<1200,300+1.5*(D2-800))))) =IF(D2<500,0,IF(D2<800,(D2-500)*1,IF(D2<1200,300+1.5*(D2-800),900+(1.75*(D2-1200))))) You can see the result here and download the spreadsheet here. But before ending this, you might want another example:

Monthly Pay Rate Tax Calculation
$0 – $3000 No Tax
$3001 – $4000 10% (Emoluments-3000)*0.10
$4001 – $10000 15% $100+ ((Emoluments-4000)*0.15)
$10001 – $15000 20% $1000+ ((Emoluments-10000)*0.20)
$15001 – 25% $2000+ ((Emoluments-15000)*0.25)

In this case, the conditions were already set correctly, so I can simply add the “if” conditions one at a time:

=IF(H11<=3000,0,1) =IF(H11<=3000,0,IF(H11<=4000,0.1*(H11-3000),"") =IF(H11<=3000,0,IF(H11<=4000,0.1*(H11-3000),IF(H11<=10000,100+0.15*(H11-4000)) =IF(H11<=3000,0,IF(H11<=4000,0.1*(H11-3000),IF(H11<=10000,100+0.15*(H11-4000),IF(H11<=15000,1000+0.2*(H11-10000))) =IF(H11<=3000,0,IF(H11<=4000,0.1*(H11-3000),IF(H11<=10000,100+0.15*(H11-4000),IF(H11<=15000,1000+0.2*(H11-10000),2000+0.25*(H11-15000))))) You can see the end result here and download the spreadsheet: