Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Aug 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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
    Regards,
    Rudi

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Another way to evaluate part of a formula that works in all recent versions of Excel:
    <UL><LI>Edit the formula (either in the formula bar or in the cell itself)
    <LI>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
    <LI>Press F9 to evaluate.
    <LI>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.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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!
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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!) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •