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”
Neat! thanks for this tip, may be very useful as described by the author.
Very cool, Thanks, Adam!