Nesting Function Principles

[LaRetta]

Hi everyone!

Well, I'll admit it. Sometimes I struggle to write a calculation simply because I

[BobWeaver]

For all practical purposes, the most deeply nested function will be evaluated first. The result is brought out to the next higher level where the result is used to evaluate that enclosing function, and so on until the outermost is evaluated.

As to which function you want to wrap around which, it really depends on what you are trying to accomplish. But, it helps to remember that if a function expects text as its argument, then the expression inside should have a text result. Likewise, if it expects a number as its argument, then the expression inside should have a numeric result.

When these formulae get really complicated, then it helps to start writing out the order that things need to be calculated (just like if you are writing a script). Then, it's easier to assemble everything into the final formula.

What I often do to keep things from getting confused, is create several calculated fields to produce the intermediate results. And then use these results in another calculated field to provide the final result. Doing this, you can look at all your intermediate results, to see where the problems are. When you are happy that everything is working, you can take all the intermediate formulae, and combine them into one big behemoth. However, I generally make a point of keeping my calculated field formulae limited to about 4 or 5 lines long. If the formula gets more complicated than that, I break things down into simpler intermediate calc field and leave them that way. When you come back to it a year later, it's a lot easier to see what is going on with the simpler formulae.

[LaRetta]

Hi Bob

Substitute(Substitute(LeftWords("Thank You GG ZZ", 4), "ZZ", "your help"), "GG", "for all") & "!" & ":)"

LaRetta