In this post let’s see how to understand complex formulae by adding a comment in the formula.
Few days back I met Kiran, a Friend of Mine. He works as an Analyst in one of the MNC’s and moved to Hyderabad recently due to a project. While we were discussing things related to our Job and the similarities we have in our Jobs. At that time, he posted me an issue he was facing.
What is the challenge
There are few excel reports which have formulae in it. These formulae are slightly complex due to the usage of multiple formulae where most of them are dynamic. Considering the size of data and the formulae used it is taking time to open/ modify/ save these files.
Being familiar with excel VBA, he is working on automating few modules. The formulae available in the sheet are long and he wants to break down them to understand the formula so that it will be easy to build the code in VBA. Due to the complexity, after one stage it so happens that he forgets what the first block of formula does and he needs to go back and check again.
He want to know if there is a possible way to provide information after every block in the formula itself so that it will be easy for him to understand/ identify what the block does without wasting time in researching again. The challenge here is the output of the formula should not be impacted upon adding information.
Let us see how to deal with this problem.
One way to deal this problem is to insert a comment in the formula itself.
How to Add Comment in Formula?
Excel has a Function named “N” which helps to add a comment. This comment is not the normal cell comment we add in excel. It is slightly different as we use a function to insert comment in the formula itself.
The Syntax for N is as follows:
=N(Value)
The comment you want should be placed in between “ “.
For Example, =SUM(B5:G5)+N(“The formula before this sum up values from multiple cells”)
How do this work?
N Function returns 0, adding zero to anything keeps the value unchanged. So we will get the sum of values in cells B5 to G5.
While this function makes the formula look bigger, this is particularly helpful to prevent getting lost while writing/understanding complex/ long formula.
Do you know of other options to add comments in Formulas. Share your thoughts in the comments box below.