Add comments/description inside a formula

Question

Right now I make another sheet in the document and then write there the more complex formulas with short descriptions of what they do. The problem with this method is that it takes time to correctly reference each cell and sometimes is even confusing as you need to go back and forth between the formula and the description.

I have already tried with comments and notes, but they are not ideal. Comments can be easily closed. While comments and notes are both visually disruptive and confusing for the not intended users.

So is there a smarter way to add a comment/description inside a formula?

Answer 1

One CAN add comments/descriptions inside a formula.

Please use one of the following patterns

• When the formula produces text results use: =YourFormula&T(N("comment"))
• When the formula produces numerical results use: =YourFormula+N("comment")

Examples

NUMERICAL results

A	B	C	D	Lambda sums
	3	5	7	15
	4	3	5	12
	1	2	4	7
sums	8	10	16

For the sum of B one can use =SUM(B1:B3)
To add a comment use this pattern =SUM(B1:B3)+N("this is for numbers")
Even inside a LAMBDA formula =BYROW(C107:F109,LAMBDA(row,SUM(row)+N("results by row")))

TEXT results

A (dept.)	B (name)	just PRs
PR	John	John
HR	Mary	Ben
HR	Paul	Tom
PR	Ben
PR	Tom

To find the PR department names with comment, you can use the :

=INDEX(FILTER(B2:B6,A2:A6="PR")&T(N("just PRs")))

Following the above you can also hide your comments, thus NOT confusing the not intended users.

Answer 2

Now that we have LET in Google Sheets, we can use the same trick that can be used in Excel, the first mention of which I saw was in this thread: https://stackoverflow.com/a/67002574/18645332

=let(
rem,"insert comment here",
formula_expression)

To elaborate, you can assign a name to whatever value(s) you want within a LET (including a text string), and nowhere does it say that you are actually obliged to use these names in the final formula expression... So alongside the benefit that LET gives you in terms of labelling parts of the formula expression with descriptive names (in a more easy to understand way than is possible with LAMBDA), you can effectively add in-line REM statements as well.