Skip to content

Add comments to Excel formulae

G29th September 2016

C2 Comments


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.



2 responses to “Add comments to Excel formulae”

  • Written by Chuck Hamdan on 6 September 2018:

    Neat! thanks for this tip, may be very useful as described by the author.

  • Written by Dan on 24 August 2019:

    Very cool, Thanks, Adam!

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.