Using the FORMULATEXT and N functions for worksheet and formula documentation

- Providing documentation for a worksheet is sometimes a cumbersome process, and not exactly exciting. But let's say, that in this worksheet, you've got some formulas, and you'd like to make sure that others who are using it understand what's going on. I've got formulas in column H, let me zoom in by holding down ctrl and moving the mouse wheel. Formula in column H, that in effect says, bonuses are going to be achieved, by one of two methods, possibly both. If your job rating is over 3, you get a $2000 bonus.

If your years of service are over 14, you get a $500 bonus. And notice how they're being added, so possibly, you'll have a bonus of as much as 2500, or if not, maybe only 2000, or only 500. We see some of those results below. But others might be looking at this, and we want to expand this, and explain what's going on. So, double clicking here. A function called N, think for the word note, maybe. +n, as you type it, doesn't have to be capitalized.

Then in (", and practically anything you wanna type. So, this is a Special bonus, just for 2015, for years of service > 14. Say it any way I wish, "). So all we're doing is using the N function to put in some text to explain what's going on. It's not changing the result here, and I'll double click, copy this down the column.

But it explains what's going on, and I could have done this by putting in additional information about the first part of the bonus as well, the part relating to Job Rating. But nevertheless, you can see what's going on here. Now, if you hadn't heard about that function, and somehow discovered it from the formulas tab in the ribbon, if you go to more functions. It is in the category called information, but the description here doesn't even come close to what we just used it for. It has other uses, I don't think many people use this function at all, in fact, it's there for compatibility with some other and older versions of Excel, it converts non-number value to a number, that's not even close to what we did.

So we used it for documentation purposes. Now, another approach to this, is actually displaying the formula next to a cell. Now, one way to do this is to double click a cell, highlight all the data while editing, press ctrl+c, escape, go to an adjacent cell, put in the spacebar, and then a ctrl V. In this case it's pretty long, you wouldn't see it all, unless you made the column really, really wide, so, not a great use of that capability there.

There is another feature that might work better, though. Although, with a long entry, it's gonna be pretty tough. We've got some statistics here, calculating something here, but we're not quite sure what. And what is the function being applied here? It's going to tabulate an average here, for the data that we see in the worksheet to the left, based on something to do with cell L 2. Now, sometimes rather than actually explaining the function, what if we could display it here? Now, when we display a function, the actual function itself, and you can do that with a shortcut called ctrl ~, keystroke shortcut ctrl ~.

That displays the function, but while it's being displayed, we're not seeing the result. So we can press ctrl ~ again, come back here. Here's an alternate, now we can put this right next to the formula, we could put it on the left side, possibly. And the words we could make column, an example here. We could make column K a lot narrower, trying it this way. And just have these. And then to the left of this, we could display what this formula's doing. I'm going to put it off to the right, since we have unlimited text.

And I'll do it for all these, because we see different kind of functions here. I'll just say they're being used in a different way. So, you can display what a formula is doing with the function =formulatext. Now, it's not always that you're doing this for multiple cells, but I'm doing it for four cells at once here, by highlighting those cells, typing in formulatext, ( and referring to cell K2, and I'll simply press ctrl enter. Now, that's going to stay there. And if you change your mind, for example, as to what the function is going to be doing, so for example, I might come back here and say, well, rather than doing an AVERAGE here, maybe I'm more interested in a sum instead, so I'll change that to sum.

Obviously it's gonna be a different total, and because this function right here is called FORMULATEXT, it's reflecting what that formula is, we see it being displayed. So, it provides documentation in a different kind of way, obviously, than the N function. But this is valuable at times, and again, it depends upon the environment that you're working in, and how much documentation you really need. But being able to see a function, and seeing the actual formula being displayed could be helpful. Now, you can also make a case in this example, since the function itself is using column L, full time, we don't necessarily have to have this here.

Most of the time, when I see formula text used, it's used right next to the data. But you can put it anywhere you want, so I could possibly move these over here, and then take this data and move it here. It all depends upon what's likely to work best for you. Once again, making the columns a little bit wider, and so on. So now we have formula text in each case, referring to the formula that's in the cell to its adjacent left. Formula text and the letter N, two different functions used for documentation.