Ability to comment within formulas
Allow us to add commentary to formulas. While it is best practice to break up large formulas over multiple cells, sometimes it has been necessary that I must create large (or just simply complex) formulas. The ability to add commentary would help tremendously for my users or future administrators who are tasked with updating or understanding my financial models.
Bruce R commented
Fully agree, inline commenting would be great. I use excel extensively for calculations & the base model is hard work for checking formulas. Example below is what I can achieve within the current infrastructure without running VBA, but VBA wont' work on Excel app.
=C78*C79*(C74-C75)/1000+N("q = UxAxDT/1000")
The above has the big issue that I see occasionally.
=C78*C79*(C74-C75)+N("q = UxAxDT/1000")/1000. It would be great if like everyone else is suggesting something like.
=C78*C79*(C74-C75)/1000#q = UxAxDT/1000# where #......# is shown in the commenting color, could just use the ' key as this already means treat as text.
Also what fantastic suggestions to allow indenting & line formatting to make formulas easier to read (please keep the ALT+TAB). But expand on this idea further.
Backwards compatibility can be kept by stripping out the additional formatting.
Col Delane commented
I too use (occasionally) the N function to add in-formula comments to explain the formula, in preference to a Cell Comment which can make your worksheet look like it's got chickenpox!!
I very regularly use Alt+Enter to wrap a multi-legged formula over multiple lines to aid visibility/debugging. To make this visibility even better, I utilise some VBA code developed by user PeterG I found in 2015 at http://excelusergroup.org/forums/p/2791/9610.aspx (though this url seems to fail now!) which automatically dynamically sizes (expands and collapses) the formula bar to display all lines when the cell is selected.
I agree with miko 100% - Please DON'T change Alt+Enter
Please don't encourage them to change the current use of pressing Alt+Enter.
It is REALLY handy for it to be non-automatic, so you can use it in situations like the below, where formulae with several repetitive clauses can be easily edited and compared:
=IF( ROUND(SUM(EOS198),0) = ROUND(SUM(EOM198),0) +1, 0,
(IF(EOS198=1, ((CWM198 -(CWM198*DXF198))*$AQJ198) +
(IF(EOS198=2, ((CWG198 -(CWG198*DXF198))*$AQJ198) +
(IF(EOS198=3, ((CWA198 -(CWA198*DWZ198))*$AQJ198) +
(IF(EOS198=4, ((CVU198 -(CVU198*DWZ198))*$AQJ198) +
(IF(EOS198=5, ((CVO198 -(CVO198*DWZ198))*$AQJ198) )
io'm all for comments, but NOT automated [new line]ing
I'm in the middle of a large project where I'm recreating our Excel calculation workbook to use a new data source and to use named ranges in the equations. I found that having an IF statement where I intentionally didn't want an output for one condition confusing. To help me I created a named range "A_COMMENT" and set it to TRUE or FALSE. When TRUE the cell would show the text so I could just read why the cell would be empty in real use. A formula would look something like this:
IF(A_COMMENT,"No Error: Explanation of why this is the desired result.",""),
ROUND( ( ( NUM_LL1 +
NUM_LL3 ) - ( 14.7 * ( 1 - 1 / NUMBER_ONE ) ) ) / ( ( 0.99 * NUMBER_ONE ) + 0.57 ), 0 )
I'm afraid that the formatting (which looks great to me) is going to lose the indents but the point of this is the A_COMMENT - it works as a comment and also provides feedback during the debugging of an equation.
This really helps when you have many nested IF with many places where you want an "empty" result. Of course each one comes about due to different conditions and with these comments you can verify that the expected logic was applied to get the "empty" result.
I found several of the legacy IF statements were giving the correct results but for the wrong reasons. This helped explain why I had difficulties maintaining those equations in the past.
In addition to Mitch's reply;
@Justin See: you can do this today, it just isn't automatic. Just hit Alt-Enter where you want the linebreaks to occur. I do this regularly for some of my most complex formulas. Maybe MS could automate it, but I'd hate it if their choice of where to put automated linebreaks was different than my own preferences. For example (in your example), they might also break up the index statement, etc. making it overly vertical and even harder to follow.
A.C. Wilson commented
Wanted this for ~20 years now.
Justin See commented
One of the biggest problems with excel is when you have beastly formulas in the formula bar that are very hard to read when passing off the file. Similar to VBA or any other coding language, it would be AMAZING if the formula bar had a button you could click that switched the formula into a code-like view.
For example, take a complex formula like this:
And make it look like this:
Obviously that's not a perfect example and the formula bar would have to be able to expand as needed. It could open up an entire world of more readable excel formulas, and it could make writing them much easier as well.
Justin See commented
Hmm, after I posted the idea the layout of the "nice" way definitely didn't stay the same. It would have much better tabbing based on the parentheses and formulas.
Kenneth Barber commented
Mitch Judd's workaround is nice, but it would still be a bit cleaner and less confusing to have an explicit comment. After all, in Mitch's case, the comment style changes for numbers versus text--not good.
Mitch Judd commented
Actually there is already a way to comment - Check out T() function and N() function. I use these to document why I use a value or as reminder in text. I use them mainly because I hate to see the little red triangles in the corner of cells.
=723*4+N("I just made a comment inside a formula")
="Lodge dues"&T(N("This is due every first Monday in November"))