Skip to content

Dear Internet Explorer user: Your browser is no longer supported

Please switch to a modern browser such as Microsoft Edge, Mozilla Firefox or Google Chrome to view this website's content.

Add comments to Excel formulae

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

2 responses to “Add comments to Excel formulae”

On 6 September 2018, Chuck Hamdan wrote: Hyperlink chain icon

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

Reply

On 24 August 2019, Dan wrote: Hyperlink chain icon

Very cool, Thanks, Adam!

Reply

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.