Internet Toolkit/Internet Toolkit.iso/tutorial/pc_learn/sptips.tut

Text File | 1993-08-16 | 17.3 KB | 354 lines

       The following information is reprinted with permission, Ultimate
       Power Tips 1.0A (c) 1992, 1993  Paul Scanlon, Scanlon Enterprises

       ----------------------------------------------------------------

                            SPREADSHEET POWER TIPS

       ----------------------------------------------------------------

        Fast Navigation in 1-2-3

        Making changes to a massive 1-2-3 worksheet can require you to
        travel to a distant cell. If you forget where you started out,
        finding your way back, involves time consuming keystrokes.
        Before you begin a worksheet session, tap the <Plus> key. Once
        you find the data you're looking for, tap <ESC>, and the cursor
        jumps back to the starting point. Tap <ESC> again, and the plus
        sign disappears.



        Highlight Key Data in 1-2-3

        If your spreadsheets contain a multitude of individual line
        items, it's sometimes difficult for co-workers to spot an
        important bit of information, such as net income. You can use 1-
        2-3's range unprotect command to highlight individual cells.
        Place the cell pointer on the desired cell, issue "/Range
        Unprotect", and press <ENTER> twice. On a color monitor the cell
        appears green; a monochrome monitor makes the cell brighter.



        To Cell and Back with 1-2-3

        With 1-2-3's Goto key, you can travel to any part of the
        worksheet. Press {F5} and use the cursor navigation keys to go
        to the next spot. To return to your starting point, just press
        {ESC}.



        Peruse and Use Faraway 1-2-3 Cells


        Here's a way to display and use a distant cell of a large 1-2-3
        worksheet. At the Ready mode, type a plus sign and the cell
        address or range name, then press Calc. 1-2-3 will display the
        current value of the cell on the control panel. Press <ENTER> to
        store the value in the cell containing the cursor, or press
        <ESC> to clear the control panel and return to the Ready mode.



        Revealing 1-2-3 Formulas

        To view 1-2-3 formulas, users usually place the cell pointer on
        a cell and look in the control panel. If you want to see a
        formula in its cell, simply place the pointer on the cell and
        issue /RANGE FORMAT TEXT, press {Enter} then {Enter} again. 1-2-
        3 displays the formula in that cell.



        1-2-3 Copy Controls Cursors

        1-2-3 users usually initiate the Copy command with the cell
        pointer in some cell in the source range. After 1-2-3 copies the
        range, it automatically returns the pointer. To have the pointer
        end up in the upper left cell of the target range, put the
        pointer there before you enter "/Copy" Then press "<ESC>" or
        <Backspace>, highlight the source range, and press <ENTER>
        twice.



        List 1-2-3 Named Ranges

        To view a list of named ranges in a 1-2-3 worksheet, press <F5>
        immediately followed by <F3>. Then select a range to go to or
        punch <F3> again to see the next five ranges. This technique is
        also useful when 1-2-3 prompts you to enter a range and you want
        to specify a previously created name.



        Find Optimal Solutions With What-If Solver

        Let's say, you're the treasurer of a pension fund. Your goal, is
        to invest your members' deposits, and reap the maximum risk.
        But, each investment presents different interest rates,
        maturation periods and risk levels, How do you decide where to
        put the money? You could use the time-honored (but imprecise)
        method of plugging guesses into data cells until the numbers
        "looked" right! But there's a better way, What-If Solver, is a
        1-2-3 add-in that finds optimal solutions for spreadsheet
        models. What-If Solver's nonlinear algorithms can handle a much
        broader range of problems than the linear methods found in
        Quattro or SuperCalc, and because its intuitive menus shield you
        from the esoteric of advanced mathematics, What-If Solver is
        much easier to use. Unlike similar products, What-If Solver
        works just fine on existing spreadsheet models and the
        documentation provides a number of clearly described case
        studies. What-If Solver is available from Frontline Systems, 140
        University Ave #100, Palo Alto, CA 94301, [phone] ext. 55
        or [phone] (by fax)...



        Spruce Up Your Charts With Text and Labels


        Adding Free-Floating text to Excel charts is easy, but how to do
        it is not immediately obvious. Just select the chart and start
        typing. The text will appear in the formula bar. Press <ENTER>,
        and drag the new text item into place. Choose Format Text to
        alter its font or colors. If you want to emphasize the texts'
        subject with an arrow, choose Chart Add Arrow, then click on the
        end of the arrow and drag it into position. You can also use
        Excel's default label options. To put a title at the top of your
        chart, select Chart Attach Text Title, type the appropriate
        text, and press <ENTER>. Similarly, you can label the x and y
        axes using Chart Attach Text Category Axes and Chart Text Value
        Axes, respectively. You can alter an attached label's appearance
        with Format Text, but if you want to change its position, you
        must replace it with a Free-Floating label.



        Getting Weekdays into Your Spreadsheet

        Sometimes, it is required to list weekdays, and only weekdays,
        for a given month. Instead of looking at a calendar and manually
        entering numbers, you can use the following trick that
        automatically list days of the month, excluding weekends. To use
        this technique, enter a year value, such as "91" in cell C1.
        Enter a month (range of 1 to 12) in cell C2. You can use other
        cells, but you'll have to alter the cell numbers in this
        method's formula. Now move to cell C4 and enter the following
        formula: @IF( @MOD( @DATE( $C$1, $C$2,1),7) > 1,1,@IF( @MOD(
        @DATE( $C$1,$C$2,1),7) = 1,2,2)). Drop down one row to cell C5
        and enter @IF( @MOD( @DATE( $C$1, C4 + 1),7) > 1,1,@IF( @MOD(
        @DATE( $C$1,C4 + 1),7) = 1,2+C4,3 + C4)). Finally, replicate
        cell C5 to cells C6 through C26. This will display the weekday
        numbers for the month, which you  can use in expressions or
        simply as row labels. To skip holidays that occur during the
        week, simply enter the next day number manually. For example, if
        July 4th falls on a weekday, move to the cell with "4" and enter
        "5" to replace the formula. Subsequent cells will automatically
        be updated.



        Smart Consolidations

        With Excel 3.0, you can quickly consolidate totals from widely
        separate sections of a large worksheet or from a group of
        worksheets. First, open the relevant worksheet(s) and select the
        range where you want to consolidate the totals. Choose Data
        Consolidate, which brings up the Consolidate dialog box. Select
        the first range containing an amount to be aggregated, and click
        on the Add button in the dialog box. Repeat this process for all
        the relevant sections or files. Then click OK, or press <ENTER>
        to consolidate the totals. You can streamline the selection
        process when you've consolidating date from multiple files. Use
        similar file names for the relevant files (let's day they all
        begin with SALES) and the same cell reference for data
        consolidate, B4:B12 for example. Enter "SALES * .XLS!$B$4:$B$12"
        in the Reference text box, and Excel will include totals from
        all worksheets, even un-opened ones, whose names begin with the
        selected range (SALES example).



        Speed Up Your Macros With ECHO

        By default, Excel continuously displays a macro's progress on
        screen. Lengthy macros operate much faster when you use the

        ECHO(FALSE) function to stop these time wasting screen updates.
        Don't use ECHO(FALSE) while debugging a long macro, or you won't
        know when it hits a snag. When you know a major section of the
        macro or subroutine is reliable, however, you can surround that
        section with ECHO(FALSE) at the start and ECHO(TRUE) at the end.
        When you're though debugging, use Formula Replace to strip out
        the scattered ECHO statements.



        Getting the Right Results With Visual Rounding

        Sometimes, when combining figures from several sections of one
        spreadsheet, or from linked spreadsheets, the totals are wrong.
        The problem, seems to be related to rounding. How can this
        problem be avoided ? Most times, you round numbers, to make them
        easier to read, but you don't want  to change the value of the
        numbers themselves. In that case, use format commands to limit
        the number of decimal places the spreadsheet should reveal. With
        "visual rounding" the numbers look the way you want but keep
        their exact value. If you use a rounding function, such as
        @ROUND, in 1-2-3 and Quatro Pro, or =ROUND in Excel, you change
        a number's value. Think of this as "mathematical rounding". If
        your report summaries contain formulas which add up tens or
        hundreds of values, rounded off with ROUND functions, your
        results can be inaccurate. But if you round all values that
        contribute to the to the final sum, visually rather than
        mathematically, your summaries would be on target. To see the
        difference, set up the following :

              A            B           C           D
        1
        2            $57,453.23   $57,453.23
        3            $29,492.98   $29,492,98
        4            $60,883.33   $60,883.33
        5            ========     ========
        6

        In 1-2-3 and Quatro Pro, enter @ROUND(@SUM(B2..B4),0) into cell
        B6. In Excel, use the formula =ROUND(SUM(B2:B4),0). The result
        is a value rounded mathematically, to the nearest dollar, with
        no record of whether the number was rounded up or down, or of
        what happened to the missing decimal places. Now, sum the values
        in Cells C2 to C4, and display the rounded result without losing
        track of the change. In 1-2-3 and Quatro Pro, enter @SUM(C2..C4)
        into cell C6. In Excel, enter =SUM(C2:C4). Then in 1-2-3, select
        /Range Format Currency, enter 0 and indicate cell C6. In Quatro
        Pro, indicate cell C6, select Style Numeric Format Currency,
        enter 0 and click enter. In Excel, indicate cell C6, select
        Format, Number and apply the format option that reads
        "$#,##0_);($#,##0). The formatted number is rounded visually,
        not mathematically. The cell format suppresses the decimal
        places and rounds to the nearest dollar.



        Quick Totals for 1-2-3

        After you have entered a column of numbers in 1-2-3 or a
        compatible spreadsheet program, you can use this macro to place
        a dashed line and an @SUM() function beneath the column quickly
        and easily. Enter the label "\-{down}@sum ({up2}.{end} {up})~ in
        an out of the way cell. Use "/Range Name Create" to name the
        range "\s". To use the macro, place the cell pointer in the cell
        directly below the column of numbers you want summed, and press
        <Alt>-S.



        Check Behind Lotus Express

        Selecting any Lotus Express accessory (Reader, Comm_Manager,
        etc...) pops up a window that obscures whatever else is on the
        screen. By pressing both {Shift} keys simultaneously, you can
        make the window temporarily disappear so that you can view the
        DOS prompt, application, or other Express accessory behind it.
        Releasing one or both {Shift} keys restores the current pop up
        window.



        Streamline Excel Macros With Subroutines

        Often a macro must repeat the same series of commands over and
        over. It's easier and more efficient to make those commands a
        subroutine, a macro within a macro. Here's a simple example.
        Suppose you want a macro to format several selected columns as
        percentages. First, use File New Macro sheet, and choose an out
        of the way area, in this example we'll start at cell A100. To
        name th subroutine, enter "FormulaPercent()" in cell A100,
        select Formula Define name, click on Command, and press <ENTER>.
        Type "=select("c")" in cell A101 and "=format.number("0.00%")"
        in cell A102. End the subroutine by entering "=return()" in cell
        A103. Now whenever you want a macro to apply the percentage
        format to the current column, in your worksheet, simply use the
        formula "=FormatPercent()" in that macro.



        Ranking Values With Excel

        Excel's powerful array feature lets you create a formula to
        compute ranks, and the ranks will adjust automatically if your
        numbers change. If the values to be ranked are in A1:A10, for
        example, type the following formula into cell B1:
        "=SUM(IF(A1>SAS:SAS10,1))+1" then press {Shift}&{Ctrl}&{Enter}
        to make this an array formula, indicated by surrounding curly
        braces. If braces do not appear, press {F2} and try again. Copy
        the formula to B2:B10, and the ranks will be calculated. With
        large ranges, however, recalculating these formulas can take a
        while, so you'll be wise to switch to manual recalc mode.



        Setting Date Formats in Worksheet Headers and Footers

        To print the current date in your 1-2-3 or Quattro Pro
        worksheet, place @ in the header or footer. The format of the
        printed date is determined by the format you select for the
        clock display on the status line. Reset the date's format with
        "/Options|Other|Clock" in Quattro Pro or "/Worksheet|Global |
        Default|Other|Clock" in 1-2-3.



        The Last Day of the Month

        Obtaining the Last Day of the Month in an expression similar to
        "@END_MONTH (argument)" or the beginning of the month via
        "@BEG_MONTH (argument)" as a serial date number can be
        accomplished, in Excel, 1-2-3 and Quatro Pro. In Excel, use the
        formula "=DATE(YEAR(A2),MONTH(A2),1) to obtain the first day of

        a month, and "=DATE(YEAR(A2),MONTH(A2)+1,1)-1 for the last day
        of a month. 'A2' is the cell name containing the serial date,
        such as 32283 for 2/1491.  For 1-2-3 and Quatro, you can use
        "@DAY(YEAR(A2),MONTH(A2),1)" to get the first day of the month,
        but, you will have to use a manually constructed lookup table to
        find the serial number of the last day of the month. Using a
        manual table method, is NOT as reliable as the Excel formula, so
        it must contain error trapping to avoid out-of-range dates. To
        build the table, open a blank worksheet and enter the labels
        "FIRST", "DATE", "DAYS", "LAST", and "DATE", in cells A1 through
        E1. In A2 through E2, enter '0', '0', 'ERR', 'ERR' and 'ERR'. In
        cell A3, enter the formula "@DATE(90,1,1)" (assuming January
        1990 is the earliest month you need in the table). Enter "+A3"
        in cell B3. In cell C3, enter the number of days for the month
        of the date in A3. In cell D3, enter "+A3+C3-1". In cell E3,
        enter "+D3". Now copy the formula of B3-E3 to cells B4 - E27
        (rows 4 thru 27, columns B thru E), using "/CB3,E3 <ENTER>B4,E27
        <ENTER>". In cell A4, enter "+A3+C3", and enter "/CA4 <ENTER>
        A5,A27 <ENTER>" to duplicate the formula to cells A5 = A27. Set
        the cell pointer to cell B3 and enter "RFD4<END> <CURSOR
        DOWN><ENTER>" to format the entries in column B as "long
        international" dates. Place the pointer in cell E3 and repeat
        the date format keystrokes. Locate to cell C4, the number of
        days in February 1990 and press <Cursor Down>. Continue entering
        the appropriate number of days for each month. Finally, in cell
        C27, D27 & E27 enter 'ERR' and the table 'MONTH' with "\RNCMONTH
        <ENTER> A1,E27 <ENTER>". To use the table, enter a serial date
        (such as 33232) in cell A30, and the formula
        "@VLOOKUP(A30,MONTH,3)" in cell B30


       Tutorial finished. Have you registered PC-Learn to receive your
       bonus disks? Registration is encouraged. Shareware works on the
       honor system! Send $25 to Seattle Scientific Photography,
       Department PCL6, PO Box 1506, Mercer Island, WA 98040. Latest
       version of PC-Learn and two bonus disks shipped promptly!