98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel training courses - Comments added within a formula
excel training courses - Comments added within a formula
Resolved · Low Priority · Version Standard
Wed 4 Jul 2007, 15:09Delegate Louise said...
Louise has attended:
Excel Advanced course
Comments added within a formula
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.
Thu 5 Jul 2007, 13:58Trainer Rich said...
RE: comments added within a formula
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
Thu 5 Jul 2007, 14:55Delegate Louise said...
RE: comments added within a formula
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 &.
Thu 5 Jul 2007, 15:01Delegate Louise said...
RE: comments added within a formula
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
Thu 5 Jul 2007, 16:27Trainer Rich said...
RE: comments added within a formula
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
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:How to Spell Check an Excel 2010 WorksheetExcel 2010 does not automatically spell check a document. So, here's how to manually spell check a worksheet. |
