I recall on the advanced course that there was a formula or 'prefix' that you could put in front of some text within a formula (ie not in a comment box) but I didn't note it down.

I'd like to use it for adding an explanation of what and how a formula reads and manipulates the data so that it is easier for colleagues to follow the process.

I don't want to use the comments box as this is more engine room stuff, like an internal instruction manual.

thanks.

Hi Louise,

You can add a single quote (') before the formula. This will turn your formula into a text string, and won't show the answer to the calculate.

Sorry if this sounds a little basic, but it sounds like it would achieve the result you're after.

eg. a cell with =6+4 will show as 10
a cell with '=6+4 will show as =6+4

This allows you to type a formula without actually calculating the result.

If this is not what you had in mind, please reply with a specific example of the result you require, to assist us in understanding your question.

If this answer does solve your problem, please mark this question as resolved. Thanks.

Regards, Rich

Sorry, but that isn't what I meant.

There was a formula that you could tag onto the end of any other formula that was like adding a comment at the end of the formula.

It didn't affect the formula in any way but it meant that you could embed the cell with an explanation as to what that cell was doing, which can be very very useful if you have long, nested IFs and ANDs and lookups etc.

And it avoids overusing the comments boxes for every cell.

Or did I imagine it? I think it had an N in it somewhere to indicate text was following, like you would use a $ or an &.

Found it!

=A1-B1+N("find the difference")+SUM(C:C)+N("then add
remaining expenses")

which is same as

=A1-B1+SUM(C:C)

thanks for your help

Louise

Louise,

Glad you found it. Just be careful with that N() function though, as it's actual purpose is to returns a value converted to a number.

Using the function list this: N(value) produces these results:

If value is or refers to a number; N returns that number
If value is or refers to a date, in one of the built-in date formats available in Microsoft Excel; N returns The serial number of that date

If value is or refers to TRUE; N returns 1
If value is or refers to FALSE; N returns 0

If value is or refers to an error value, such as #DIV/0!; N returns the error value

If value is or refers to anything else; N returns 0

This last result is what would normally happen if you had a text string in your example. So just be aware you are actually adding on 0 in your formula.

I'll mark this question as resolved.

Regards, Rich