Skip to content

Add comments to Excel formulae

G29th September 2016

CNo Comments

TExcel

Are your Excel formulas getting out of control? Here’s an easy method to insert comments, so that you can keep track of what’s what.

Are you regularly working with long Excel formulae? I certainly am: Many of mine will run into multiple lines such as the example below:

=IFERROR(SUM((IF([@Treatment]="Control", "NA", MAX(IF((([@Row]=TEXT(DroughtSpotter[Row],0))*(([@Column]=TEXT(DroughtSpotter[Column],0)))*(DATEVALUE(DroughtSpotter[Time_Corrected])=(MIN(DATEVALUE(DroughtSpotter[Time_Corrected])+1)))*(TIMEVALUE(DroughtSpotter[Time_Corrected])>0.5)*(TIMEVALUE(DroughtSpotter[Time_Corrected])<0.75)),DroughtSpotter[Weight_g]))))-(IF([@Treatment]="Control", "NA", MIN(IF((([@Row]=TEXT(DroughtSpotter[Row],0))*(([@Column]=TEXT(DroughtSpotter[Column],0)))*(DATEVALUE(DroughtSpotter[Time_Corrected])=(MIN(DATEVALUE(DroughtSpotter[Time_Corrected])+1)))*(TIMEVALUE(DroughtSpotter[Time_Corrected])>0.5)*(TIMEVALUE(DroughtSpotter[Time_Corrected])<0.75)),DroughtSpotter[Weight_g]))))), "NA")

When formulae become this long, it becomes very difficult to keep track of the various components of each equation.
Thankfully there is a very simple solution to this problem.

The N function

In Microsoft Excel, the N function returns a value converted to a number. Conveniently, the function can be 'hacked' into a comment by including text inside quotation marks within the function:

=N("Insert Comment Here")

This will return a value of 0, which for most functions is not a problem. To implement the function, simply add it to your existing equation as per the example below:

=SUM(IF((([@Row]=TEXT(DroughtSpotter[Row],0))*(([@Column]=TEXT(DroughtSpotter[Column],0)))),DroughtSpotter[Lighting])+N("Insert Comment Here"))

Now you can add useful comments throughout your long Excel formulae to keep track of each component without interfering with the equation itself.

   

Comments:

No comments have yet been submitted. Be the first!

Have your say:

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.