Recently I had a client that wanted to automatically calculate rental increases based on the date that the lease expired.

He didn’t have a large amount of criteria, so I used an If-Else test to evaluate which percentage increase should be applied to the rental amount.

We ended up with a spreadsheet that looked like this:

This used two functions:

  • IF(Condition;Perform if TRUE; Perform if FALSE)
    For a full explanation of the IF() statement, check out the OpenOffice Documentation Wiki
  • DAYS360(start date; end date; date type) – used to compare if one date is older than another
    For a full explanation of the IF() statement, check out the OpenOffice Documentation Wiki

IF() Statement Fundamentals – Example

In this case, I was comparing three dates, so I had to nest the statements. But to start with, let’s look at the first IF statement:

IF(DAYS360($A$3;B8;0)<0;Condition 1;Condition 2)

A3 is 1.Nov.2009. B8 is 16.Jun.2009. Type is 0 because I am using the US mm/dd/yyyy format. Set type=1 if you are using the European dd/mm/yyyy format.

So, DAYS360(1.Nov.2009;16.Jun.2009;0) is -135. The result is a negative (<0), so therefore 16.Jun.2009 is before 1.Nov.2009. If it was a positive, then the 2nd date would be after the first date). Because the test returned a TRUE result, we then perform condition 1 in my IF statement. If the test returned a FALSE result, then it will perform the 2nd Condition.

IF() Statement – Advance on to Nested IF()s

In this case, the condition tested true, so the rental increase is calculated at 10% (C8*(1+$B$3)). However if the result returns FALSE, I then need to test the next date. To do this I nested another IF() Statement.

I only want the other dates to be tested if the first IF() statement returns FALSE, so therefore it is inserted as the second condition in my statement above.

IF(DAYS360($A$3;B8;0)<0;Condition 1;Condition 2)

So my full IF() statement, with the nested IF() statement is in the format as follows:

IF(DAYS360($A$3;B8;0)<0;Condition 1;IF(DAYS360($A$3;B8;0)<0;Condition 1;Condition 2))

So for my friend’s rental price increases:


As you can see in the “Increase %” column below (which I used to check my calculations – I’ll delete it later), the percentage being applied to the rental increase is correct, as defined by the rules at the top of the spreadsheet.

If you wish to download this spreadsheet to see how it works, please download the If-Else-Statement Spreadsheet here.

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.