Fast Tips for Function Palette, Arguments + Nests (Excel 2000 >)

Topic

New Reply

This post couldn’t have come at a better time for me, as I’m setting up a new spreadsheet with some moderately complicated formulas. Many a time I have typed out a complex formula, hit OK… and then had to pick back through to find my mistake, because Excel found an error in what I had typed. #3 below will be a huge help in sorting out those problems.

Thanks Rudi!

Reply | Quote

Replies

Its nice to get feedback like this. Tx for your comments Cris.

PS:
I mentioned in the Title that these tips are for Excel 2000> , but if you are working in Excel XP or higher, then you may also find the Evaluate Formula command very useful. Select the function in question and choose Tools : Formula Auditing : Evaluate Formula. This opens a dialog that allows you to step into the function and evaluate each nested function induvidually to locate and fix problems. (I find it very handy and sometimes intriguing to step into Array functions. All the TRUE and FALSE values can get a bit much though!)

Cheers

Reply | Quote

Another way to evaluate part of a formula that works in all recent versions of Excel:

• Edit the formula (either in the formula bar or in the cell itself)
• Select the part you want to evaluate; it must be a logical unit, for example C3 or SUM(B1:B100), but not SUM or SUM(A
• Press F9 to evaluate.
• Repeat as needed.
  [/list]Unlike the Tools | Formula Auditing | Evaluate Formula feature, you cannot go back and forth. And if you happen to press Enter, you’ll save the formula with the evaluated parts! Fortunately, you can still use Undo to restore the original formula.

Reply | Quote

Wow…I didn’t know this! I tried this out and it works well. It changes the selected part of the formula to TRUE or FALSE.
Tx for this input!

Reply | Quote

It doesn’t necessarily evaluate parts of a formula to TRUE or FALSE. The result will depend on what you evaluate – it could be a string, a number, a boolean, or an error value.

Reply | Quote

Tx Hans

Just to re-itterate what you mentioned in your prior post…
The formula result gets overwritten as you test the parts of the nest. It is important theirfore to PRESS : ESCAPE after you have tested the areas using F9. (Else – UNDO!)

Reply | Quote

Heres a quick tip for those who are unaware of this useful function shortcut key.

1.
If you need a quick reminder of the arguments of any Excel function, simply type the equals and the function name into the cell and press CTRL+SHIFT+A. This inserts the arguments and you just double click them to select them and replace with the appropriate values.

2.
If you need assistance in creating the function and prefer the function palette to help build the function then type the equals and the name of the function into the cell and press CTRL+A, This will activate the palette for that function immediately. This is also handy if you like using the palette but get irritated in having to click FX and collect the function continually!

3.
Many people do not realise the value of the Function Palette when designing Nested Functions. The key here is to pre-plan the function as you need to build it from the outside inwards. So you need to know which functions to call up first. Once you have the order, use the palette to collect the first function. In the arguments of this function, use the name box (far left of the formula bar) to activate the next function…etc! Then…the real tip is this: Once you have built the nested function, you can switch between the palettes as easily as clicking in the name of the function in the formula bar. What ever function name you click on, swings the palette to show that function with the added bonus of showing the result of this function in the nest and the result of the entire expression. Once all the arguments through the nest are satisfied…then you click on OK.
PS: You can even use this technique to edit existing nested functions as needed.

Cheers

Reply | Quote