The Modelling Power of Excel’s Shortest Formula =N

What is modelling power?

Clarity and reusability!

Building a Financial Model that’s easy to audit, maintain and reuse means building your model logic in a way that’s self-documenting.

Some Excel formulae give you this expressive power that makes it easy for anyone to follow your model.

And combined with proper formula anchoring, parts of your model can be easily copy pasted into additional sections of your model or even reused in other models.

What does =N do?

For modelling purposes, =N converts the values TRUE or FALSE to 1 and 0 respectively. This means you can add or multiple against these 1 and 0 “flags”.

Why is this powerful?

Aside from the sheer simplicity of being a single letter formula, =N allows you to simplify complicated and often deeply nested =IF formulae. All the layers of conditions that make up the final outcome of an =IF formula can be laid out more clearly as a series of 1 and 0 flags. Making it easy to audit each step of the conditional logic so everyone can give due thought to the deeper correctness of the model.

N simplifies a complex IF formula calculating various addons to a Sales Invoice

How to use it

= N (condition)

Where “condition” is anything that evaluates to TRUE or FALSE. N has its nuances, so any text will be converted to 0 and dates will return their integer equivalent as stored by Excel internally (e.g. 01/06/21 becomes the number 44348). In Financial Models =N is used to convert TRUE and FALSE into 1 and 0, transforming them into very powerful Binary Flags.

The real power of the Binary Flag (1 or 0) technique then really picks up once you compound multiple conditions. For example, multiplying two flags is the equivalent of using the AND formula while adding two flags together is the equivalent of the OR formula.

Flags (1 or 0) can be calculated (which is where =N comes in). E.g. based on dates you can calculate flags to define which months are “actual” vs “forecast”.

Or even calculate when bits of a construction project, such as a multi-site solar park, come online

Flags (1 or 0) are also commonly input by the user to enable / disable something. E.g. in a list of staff certain employment benefits.

To quickly get started with this technique, next time you come across an =IF formula, think how you can change the logic to use flags instead. Those =IF formulae that just have “0” in the “value if false” bit of the formula are ripe for replacement with =N.

Other Approaches

There are other formulae such as =SWITCH, =CHOOSE and =IFS that can simplify complex conditional logic, but they neatly encompass the logic only within the cell formula itself. Whereas using =N with clearly laid out Binary Flags makes for a cleaner and more auditable presentation of complex logic; especially when models are printed out for funding and contractual purposes.

Summary and Download

=N is a simple to use formula which brings a heightened level of clarity and reusability to your model.

The syntax is very simple =N(condition). Just feed in a TRUE/FALSE condition and get a 1 or 0 flag out the other side. Now you can multiply or add these flags to build complex logic that’s cleanly presented, self-documenting and easy to audit.

Free Excel download of all the example shown above:

Leave a comment

Your email address will not be published.