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.

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.

On 6 September 2018, Chuck Hamdan wrote:

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

On 24 August 2019, Dan wrote:

Very cool, Thanks, Adam!

<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>