Loading

Everything HP200LX: Knowledge, Products, Service 

Lotus 123 fundamentalsLotus 123 is one of the most powerful and useful applications built into the HP Palmtop PC. Its great to keep lists, add up numbers, and perform a myriad of calculations automatically. Once you set up a spreadsheet and make sure it works, all you have to do is enter data. The spreadsheet takes care of everything else. Using Lotus 123 is simple, but you do need to know some fundamentals first. The best way to learn is with an example. Lets look at a small, privately owned company with three principle stockholders. Well create a spreadsheet that shows how many shares each stockholder owns and what percentage of the total shares each holds. While doing this, well cover the following basic spreadsheet tasks: 1. How to create mixed (relative and absolute) formulas. These will be used to create percentages and totals. 2. How to create a percentage formula. 3. How to copy formulas. This task makes it easy to create duplicates of the percentage formula that you will create. 4. How to format numbers. These are used to show the percentages properly. For example: 50.000%, instead of 0.5. Getting started Lets start with the spreadsheet shown in Screen 1.
Screen 1. This screen shows our Stockholder spreadsheet with labels and data already entered. First, enter the data into the appropriate cells, as shown. Create the dashed lines that appear in cells B4 to D4, and in B8 to D8 as follows:. Go to cell B4 and type the backslash key and the minus key ( \  ). This will fill the cell with dashes. Now do the same in cell B5, and so on. Creating relative formulas The basic task at hand is to first figure out the total number of shares held by the three stockholders. To do this, we add up the numbers in cells C5, C6, and C7. The percentage each owns is the number of shares they own divided by the total shares, and then multiplied by 100. With your cell pointer in cell C9, you can simply enter the following formula: @sum (C5..C7). This tells Lotus to add up the contents of all cells from C5 to C7. Another way to enter this formula is: 1. Place cell pointer on C9. 2. Type in: @sum( 3. Press the UpArrow key until the cellpointer is on C5. (On the second line of 123, up in the left hand corner, it will display @sum(C5) 4. Press the period key to anchor the first cell reference. The second line will now display @sum(C5..C5. 5. Press the DownArrow key. 123 will begin blocking out the range. Press the DownArrow key until the range C5 through C7 is blocked out. Then finish the formula by pressing the close parenthesis key and then pressing (ENTER). Cell C9 should now display the total number of shares. Creating a percentage formula Again, the percentage of shares owned is a function of the number owned and the total number of shares in existence. 1. Move your cellpointer to cell D5, where well create the first percentage formula. Notice that the Mode Indicator in the upper right hand corner of 123 says READY (i.e., Lotus is ready for you to enter data or a formula). Now press the plus key, +. Notice that the Mode Indicator changed to VALUE (because you entered a plus sign, Lotus assumes you are going to enter a value). Note that the second line in the upper left corner of the screen now has a + in it. This line displays anything you are entering. 2. Now press the Left Arrow key. Notice that the Mode indicator changed to POINT. Instead of entering a value, Lotus is letting you point at the value you want to enter. Note that the second line in the upper left corner of the screen reads +C5, and the cell pointer is highlighting cell C5. 3. Now press the divide key /. Notice that the Mode Indicator changed to VALUE. Lotus knows you need to enter another value to finish the division. Note that the formula on the second line in the upper left corner of the screen changed to +C5/, and the cell pointer went back to cell D5. 4. Now press the DownArrow key four times and the LeftArrow key once. Notice that the Mode Indicator changed to POINT, the formula on the second line in the upper left corner of the screen changes to +C5/C9, and the cell pointer highlights cell C9. 5. Then press the (F4) key, which is an absolute reference toggle switch. Notice that the formula on the second line changed to +C5/$C$9. Keep pressing (F4) slowly until the formula on the second line is +C5/C$9. Then press (ENTER). [Note: One of the nice things about Lotus is that when you copy formulas from one cell to another, Lotus adjusts the cell references when needed. Sometimes you do not want this to happen. If this is the case, you create an absolute cell reference in the formula as described in this example.] The mixed reference formula in cell D5 is now complete. Copying formulas You can reenter the above formula in Cells D6 and D7. But that takes too much time. Its easier to simply copy the formula from D5. With the cell pointer on D5: Press: (MENU) (C) (ENTER) (DownArrow) (.) (DownArrow) (ENTER). After you press the last key the spreadsheet should look like Screen 2.
Screen 2. Our stockholder spreadsheet now shows the formula from D5 copied to D6 and D7. Formatting numbers Well fix the appearance of the numbers next. 1. First lets make the spreadsheet display all numbers with commas separating thousands and displaying no decimal places. Do so by pressing: (MENU) (W) (G) (F) (,) (0) (ENTER). Unfortunately, this doesn't display the percentages properly. Up to this point, they are displayed as fractions, and .5 really means 50%. 2. Fix the appearance of the percentages by moving the cell pointer to cell D5. Then press: (MENU) R F P 4 (ENTER) (DownArrow) (DownArrow) (DownArrow) (DownArrow) (ENTER). Your spreadsheet will now look like Screen 3. You now have a template ready to insert new data.(xx start here with Merkle's Lotus 123 Fundamentals article. start with Lotus 123 is one of the most.)
Screen 3.The spreadsheet now displays the properly formatted numbers and percentages. Carl Merkle Director of Corporate Projects Shier Systems & Software, Inc. carl@shier.com


Copyright © 2010 Thaddeus Computing Inc