Adding comments to formulas

Everyone knows about adding a comment to a cell but did you know that you can add a comment directly to a formula?

This could come in handy to give users a reminder of some critical piece of information related to the formula.

For example, to remind users that the formula needs to be array entered:

Data Range

	A	B	C	D	E	F
1	Name	Y/N	Bid	-----	Max Yes	Name
2	Sue	Yes	45		77	Biff
3	Carl	Yes	30
4	Tom	No	59
5	Erin	No	61
6	Abbey	No	28
7	Biff	Yes	77
8	Lisa	No	33
9	Paul	Yes	17
10	Ken	No	77

This array formula** entered in E2:

=MAX(IF(B2:B10="Yes",C2:C10))+N("Array Entered - CSE")

This array formula** entered in F2:

=INDEX(A2:A10,MATCH(E2,IF(B2:B10="Yes",C2:C10),0))&T(N("Array Entered - CSE"))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Hi Tony Valko,

Nice Tip

But I was read in J-Walk book about the same topic and in which he achieved it using simple expressions but I am unable to recollect those from my memories.

I think it is an 2003 pdf book which I lost due to hard disk failure. Not able to recollect the book name too since I had huge collection of excel books...

I remember in that book he described about payroll model and used several formula's with comments inside it. Like "Tax" and the tax formula. But the word tax only show in formula bar to understand the calculation is related to Tax. Like that he used lot more which stands only in formula bar for understanding the formula alone.

I was searching that method in google but unable to get it

Do you have any idea other than this method?

Hello Sixthsense

Take a look to this.

http://www.excelforum.com/excel-tips...l-formula.html

Hi Fotis1991,

Thanks for the link

But still I think J-Walk done it without using N() function, I believe.

But unable to recollect that method/expressions from my memory

Originally Posted by Tony Valko

Everyone knows about adding a comment to a cell but did you know that you can add a comment directly to a formula?

Just be aware that in some complex formulae, this will throw up an error message. I have not successfully used it in formulae using the INDIRECT function, for example.

Hello Sixsense,
Are you mentioning about defined name?

Originally Posted by bebo021999

Are you mentioning about defined name?

No... It's about addding comments inside the formula for easy understanding

Originally Posted by :) Sixthsense :)

No... It's about addding comments inside the formula for easy understanding

Yep. With defined name, "Tax" (to be a comment) will be showed in formula bar while result in cell.

@ Quang,

What you are saying is right... but we know the difference between named range and the comment.

I still remember J-walk used some expressions to show comments inside the formula.

Originally Posted by AliGW

Just be aware that in some complex formulae, this will throw up an error message. I have not successfully used it in formulae using the INDIRECT function, for example.

Show me the formula you're trying to use it in.

If it's constructed correctly it should work with no errors.

Not aware of other formula methods.

A couple of weeks ago a poster was concerned about users not array entering a formula so I suggested adding the comment to the formula.

That's what prompted me to start this thread.

EDIT: Here's that thread:

http://www.excelforum.com/excel-form...-combined.html

Post #9

Originally Posted by :) Sixthsense :)

I was searching that method in google but unable to get it

Do you have any idea other than this method?

I just did a quick look at JWalk's site and didn't find anything.

Maybe you could contact him through his site and ask about this.

how about

=formula+IF(TRUE,0,"Notes") -> we'll basically same with returning 0 in N... I hope .

Yeah, that'll work!

Important notice for user:

To give comment to a blank cell like this:

=N("Do not input here")

may cause blank to non blank cell and return bad results somewhere.

@Tony,
I dont know how to say thank for the tip, just here, in #4

__________

Originally Posted by Tony Valko

Maybe you could contact him through his site and ask about this.

Thanks for the suggestion, and yes I sent a mail to him and I will update here once I receive the reply

My Query

Is there any method to add comment to formula without using N() function. I remember, you shown it in one of your excel books but unable to recollect it.

Reply from J-Walk

There’s no other method that I know about. (But you can use Excel’s normal cell comment feature, of course)

-John

Everyone please accept my apologies for mis-interpreting something on assumption

No problem.

Thanks for letting us know!

ISNUMBER() does the deed, too.