Easier way to handle your formulae: Make them look like a programming code

Almir's picture

If you don't know it already, you can write your formulae (the complex ones) in a way similar to one that programmers use to make their code more legible. It is particularly usefull when creating complex nested formulae (formula within formula), as well as the logical ones, like OR, AND and alike. Why? Well, it is very easy to get lost in long formula: Where is the beginning? What is condition? Where to put a parenthesis? Where did I make a mistake? etc.

Look at the following formula and try to figure out what it does:


Actually, it checks a cell with a date, and if it has a certain value, formula returns a value. If not, it returns another value. You certainly did figured out formula logic. The question is: how long did it take?

Look attached picture with the same formula, written in a slightly different manner.

I used only spaces for indentations and ALT+ENTER for line breaks. Don't worry: they do not affect formula.

Speaking about explanations within formula, it is usefull to have description within formula, whether you return to your formula after long time or someone else has to use it. You can insert comment without affecting formula functionality or result: simply add the following:

+ N("your description") - put your comment between quotes.

N function converts numbers and dates, but for text it returns zero, so it will not affect formula result.

Now you have a complex formula with visual representation of its logics, along with textual explanations within formula.

Same formula with indentations and explanations within