- •Оглавление
- •Introduction 2
- •Introducing Ms. Formula 10
- •Introduction Calc is a spread sheet -- what are those?
- •Open Sesame How to open a document
- •Save Me How to open a document
- •Close the Gates How to close a document
- •Introducing Ms. Formula An oversized calculator
- •Our first, real formula...
- •Average Joe The second formula
- •The time is now How to let Calc give you time
- •Extracting a given number of characters from a cells, counting from left
- •Extracting a given number of characters from a cells, counting from right
- •Extracting a given number characters, counting from the point you specify
- •How to create and use random numbers
- •How to look up values in a grid
- •How to round numbers
- •How to cut off decimals in an elegant way
- •How to sum only lines meeting your criteria
- •Vlookup(a1;b1:d100;3;0)
- •Vlookup(a1;b1:d100;3;0)
- •How to count characters in a cell or string
- •Formatting of text and cells How to change the appearance of the spreadsheet easily
- •DataPilot How to manipulate huge amounts of data easily
- •Charts How to create simple charts
Introducing Ms. Formula An oversized calculator
Although it would be a bit overkill, you can actually use Calc as a normal calculator. And since that can even help us to understand how to use formulas, let’s do just that! We’ll start off with a really tough one; how much is two plus two? Go to any cell and type =2+2 and hit [Enter]. The cell should now read 4, which should be pretty close to the solution ;-) This exercise is actually more useful than one might think, because it shows us that: - formulas are entered into cells - formulas begin with "=" - formulas are used to return something different than what we put in As we see from this example, we can add numbers quite easily, but quite differently from, say, a calculator. The nice thing here is that we can always review what actually gave the result, as opposed to most ordinary calculators. There is NO bonus prize for guessing that we can also subtract, multiply and divide in the same manner... Please remember what they tried to teach us at school, because the same rules apply in Calc; multiplication and division are done before adding and subtraction! Which means that =2+3*4 results in 14, not 20... If you want Calc to understand that you actually want to add 2 and 3 before you multiply by 4, you have to do so by using parenthesis, like this: Now we have forced Calc to add 2 and 3 before multiplying by 4, which give us 20 as the outcome. Try for yourself if you don’t believe me! I have never experienced that a spreadsheet calculates incorrectly (no matter how much I’d sometimes like to blame it instead ofme...), and messing up the parenthesis are one of the easiest mistakes to make at first.
Sum it up
Our first, real formula...
The sum formula will be our first, real formula! This is one of the definitively most widely used formulas. The formula is built like this: =sum() I guess the point of the formula is quite obvious, to "sum" something... To be more specific, to sum any number of numbers. With the sum formula you can enter the numbers to sum or you can refer to cells in which the numbers are. We’ll excamplify the latter now. OK, let’s jump right into it! Make a list of numbers like this: Now we’ll try to sum these numbers, enter the formula as following: The "=" is very important, this tells OpenOffice that this is a formula. The word sum is important because it tells Calc what to do with the numbers. The paranthesis with the cell references are used to show Calc which numbers to do something with. Now, here’s a really cool trick; start entering the forumula =sum( and nothing else! Now click on the topmost cell that contains the numbers, and hold the mouse button down while you drag down until you have marked the entire area you want to evaluate. Enter ), the ending paranthesis. What happened now? Calc actually entered the cell adresses for you! When you hit [Enter], your numbers will be summed, like this: Now, try changing the uppmost value. Do that by clicking once on the cell, and start typing the number you want entered: Now you’ll see that Calc automatically updates the sum to reflect the changes: OK, delete the sum formula by selecting the cell, hit the [Delete] button, and finally [Enter]. Re-enter the same formula as before: When you hit [Enter], you get the sum to the right of the area. This shows that you can place the sum wherever you want, which you will experience sooner or later is invaluable! Now, try to enter a new number below the number in cell A5, like below: What happened with the sum formula? Nothing! Why? Because we didn’t allow the formula to include any new number. Go to the cell which contains the formula, press [F2] to enter the edit mode. The cells inclued in the formula will be bordered to show which area is included in the formula. At the bottom right of the formula you’ll see a handle, which you now should click and hold, and drag down, so that you include also the new number you entered: Hit [Enter], and see that the formula now includes the last number you entered. Again, go to the the formula and hit [F2]. Change A6 to A1000: Now, try to enter new numbers below the others, and see what happens... You are now able to enter new numbers without having to update the formula each time! Well, until you’ve got 1000 numbers anyway... If you expect to reach that many numbers, you can increas the number from A1000 to A10000 e.g. But try it as you’ve edited now: NB: The dark marking is done to examplify new numbers.