Difference between revisions of "Documentation/OOo3 User Guides/Calc Guide/Examples of functions"

From Apache OpenOffice Wiki
Jump to: navigation, search
m (Simple statistics: del extraneous phrase, add em-dash)
(Rounding off numbers)
 
(5 intermediate revisions by the same user not shown)
Line 5: Line 5:
 
|NextPage=Documentation/OOo3_User_Guides/Calc Guide/Using regular expressions in functions
 
|NextPage=Documentation/OOo3_User_Guides/Calc Guide/Using regular expressions in functions
 
}}
 
}}
For novices, functions are one of the most intimidating features of OpenOffice.org's Calc. New users quickly learn that functions are an important feature of spreadsheets, but there are almost four hundred, and many require input that assume specialized knowledge.  Fortunately, Calc includes dozens of functions that anyone can use.
+
For novices, functions are one of the most intimidating features of OpenOffice.org's Calc. New users quickly learn that functions are an important feature of spreadsheets, but there are almost four hundred, and many require input that assumes specialized knowledge.  Fortunately, Calc includes dozens of functions that anyone can use.
  
 
== Basic arithmetic and statistic functions ==
 
== Basic arithmetic and statistic functions ==
Line 13: Line 13:
 
The simple arithmetic operations are addition, subtraction, multiplication, and division.
 
The simple arithmetic operations are addition, subtraction, multiplication, and division.
  
Except for subtraction, each of these operations has a related function. You can use SUM for addition, PRODUCT for multiplication, and QUOTIENT for division. Traditionally, subtraction does not have a function.
+
Except for subtraction, each of these operations has its own function.  
 +
*SUM for addition
 +
*PRODUCT for multiplication
 +
*QUOTIENT for division.  
 +
 
 +
Traditionally, subtraction does not have a function.
  
 
SUM, PRODUCT and QUOTIENT are useful for entering ranges of cells in the same way as any other function, with arguments in brackets after the function name.
 
SUM, PRODUCT and QUOTIENT are useful for entering ranges of cells in the same way as any other function, with arguments in brackets after the function name.
Line 54: Line 59:
 
In some cases, you may be able to get similar results to some of these functions by setting up a filter or a custom sort. However, in general, functions are more easily adjusted than filters or sorts, and provide a wide range of possibilities.
 
In some cases, you may be able to get similar results to some of these functions by setting up a filter or a custom sort. However, in general, functions are more easily adjusted than filters or sorts, and provide a wide range of possibilities.
  
At times, you may just want to enter one or more formulas temporarily in a convenient blank square, and delete it once you have satisfied your curiosity. However, if you find yourself using the same functions constantly, you should consider creating a template and including space for all the functions you use, with the cell to their left used as a label for them. Once you have created the template, you can easily update each formula as entries change, either automatically and on-the-fly or pressing the ''F9'' key to update all selected cells. The choice, really, is yours.
+
At times, you may just want to enter one or more formulas temporarily in a convenient blank square, and delete it once you have finished. However, if you find yourself using the same functions constantly, you should consider creating a template and including space for all the functions you use, with the cell to their left used as a label for them. Once you have created the template, you can easily update each formula as entries change, either automatically and on-the-fly or pressing the ''F9'' key to update all selected cells.  
  
 
No matter how you use these functions, you will probably find them simple to use and adaptable to many purposes. And, by the time you have mastered this handful, you will be ready to try more complex functions.
 
No matter how you use these functions, you will probably find them simple to use and adaptable to many purposes. And, by the time you have mastered this handful, you will be ready to try more complex functions.
Line 61: Line 66:
 
For statistical and mathematical purposes, Calc includes a variety of ways to round off numbers. If you’re a programmer, you may also be familiar with some of these methods. However, you don’t need to be a specialist to find some of these methods useful. You may want to round off for billing purposes, or because decimal places don’t translate well into the physical world—for instance, if the parts you need come in packages of 100, then the fact you only need 66 is irrelevant to you; you need to round up for ordering. By learning the options for rounding, you can make your spreadsheets more immediately useful.
 
For statistical and mathematical purposes, Calc includes a variety of ways to round off numbers. If you’re a programmer, you may also be familiar with some of these methods. However, you don’t need to be a specialist to find some of these methods useful. You may want to round off for billing purposes, or because decimal places don’t translate well into the physical world—for instance, if the parts you need come in packages of 100, then the fact you only need 66 is irrelevant to you; you need to round up for ordering. By learning the options for rounding, you can make your spreadsheets more immediately useful.
  
When you use a rounding function, you have two choices about how to set up your formulas. If you choose, you can nest a calculation within one of the rounding functions. For instance, the formula =ROUND((SUM(A1;A2)) adds the figures in cells A1 and A2, then rounds them off to the nearest whole number. But, even though you don’t need to work with exact figures every day, you may still want to refer to them occasionally. If that is the case, then you are probably better off separating the two functions, placing =SUM(A1;A2) in cell A3, and =ROUND (A3) in A4, and clearly labelling each function. Which choice you make for layout depends largely on your work habits.
+
When you use a rounding function, you have two choices about how to set up your formulas. If you choose, you can nest a calculation within one of the rounding functions. For instance, the formula =ROUND((SUM(A1;A2)) adds the figures in cells A1 and A2, then rounds them off to the nearest whole number. However, even though you don’t need to work with exact figures every day, you may still want to refer to them occasionally. If that is the case, then you are probably better off separating the two functions, placing =SUM(A1;A2) in cell A3, and =ROUND (A3) in A4, and clearly labelling each function. Which choice you make for layout depends largely on your work habits.
  
 
=== Rounding methods ===
 
=== Rounding methods ===
Line 72: Line 77:
 
Another option is the ODD and EVEN pair of functions. ODD rounds up to the nearest odd number if what is entered in the number argument is a positive number, and rounds down if it is a negative number, while EVEN does the same for an even number.
 
Another option is the ODD and EVEN pair of functions. ODD rounds up to the nearest odd number if what is entered in the number argument is a positive number, and rounds down if it is a negative number, while EVEN does the same for an even number.
  
Still another option are the CEILING and FLOOR functions. As you can guess from the names, CEILING rounds up and FLOOR rounds down. For both functions, the number that they round to is determined by the closest multiple of the number that you enter as the significance argument. For instance, if your business insurance is billed by the work week, the fact that you were only open three days one week would be irrelevant to your costs; you would still be charged for an entire week, and therefore might want to use CEILING in your monthly expenses. Conversely, if you are building customized computers and completed 4.5 in a day, your client would only be interested in the number ready to ship, so you might use FLOOR in a report of your progress.
+
Options are the CEILING and FLOOR functions. As you can guess from the names, CEILING rounds up and FLOOR rounds down. For both functions, the number that they round to is determined by the closest multiple of the number that you enter as the significance argument. For instance, if your business insurance is billed by the work week, the fact that you were only open three days one week would be irrelevant to your costs; you would still be charged for an entire week, and therefore might want to use CEILING in your monthly expenses.  
 +
 
 +
Conversely, if you are building customized computers and completed 4.5 in a day, your client would only be interested in the number ready to ship, so you might use FLOOR in a report of your progress.If cell E1 contains the value 46.7, =CEILING(E1;7) will return the value 49.
  
 
Besides number and significance, both CEILING and FLOOR include an optional argument called mode, which takes a value of 0 or 1. If mode is set to 0, and both the number and the significance are negative numbers, then the result of either function is rounded up; if it is set to 1, and both the number and the significance are negative numbers, the the results are rounded down. In other words, if the number is -11 and the significance is -5, then the result is -10 when the mode is set to 0, but -15 when set to 1.
 
Besides number and significance, both CEILING and FLOOR include an optional argument called mode, which takes a value of 0 or 1. If mode is set to 0, and both the number and the significance are negative numbers, then the result of either function is rounded up; if it is set to 1, and both the number and the significance are negative numbers, the the results are rounded down. In other words, if the number is -11 and the significance is -5, then the result is -10 when the mode is set to 0, but -15 when set to 1.

Latest revision as of 20:31, 25 December 2010



For novices, functions are one of the most intimidating features of OpenOffice.org's Calc. New users quickly learn that functions are an important feature of spreadsheets, but there are almost four hundred, and many require input that assumes specialized knowledge. Fortunately, Calc includes dozens of functions that anyone can use.

Basic arithmetic and statistic functions

The most basic functions create formulas for basic arithmetic or for evaluating numbers in a range of cells.

Basic arithmetic

The simple arithmetic operations are addition, subtraction, multiplication, and division.

Except for subtraction, each of these operations has its own function.

  • SUM for addition
  • PRODUCT for multiplication
  • QUOTIENT for division.

Traditionally, subtraction does not have a function.

SUM, PRODUCT and QUOTIENT are useful for entering ranges of cells in the same way as any other function, with arguments in brackets after the function name.

However, for basic equations, many users prefer the time-honored computer symbols for these operations, using the plus sign (+) for addition, the hyphen (–) for subtraction, the asterisk (*) for multiplication and the forward slash (/) for division. These symbols are quick to enter without requiring your hands to stray from the keyboard.

A similar choice is also available if you want to raise a number by the power of another. Instead of entering =POWER(A1;2), you can enter =A1^2.

Moreover, they have the advantage that you enter formulas with them in an order that more closely approximates human readable format than the spreadsheet-readable format used by the equivalent function. For instance, instead of entering =SUM (A1:A2), or possibly =SUM (A1;A2), you enter =A1+A2. This almost-human readable format is especially useful for compound operations, where writing =A1*(A2+A3) is briefer and easier to read than =PRODUCT(A1;SUM(A2:A3)).

The main disadvantage of using arithmetical operators is that you cannot directly use a range of cells. In other words, to enter the equivalent of =SUM (A1:A3), you would need to type =A1+A2+A3.

Otherwise, whether you use a function or an operator is largely up to you—except, of course, when you are subtracting. However, if you use spreadsheets regularly in a group setting such as a class or an office, you might want to standardize on an entry format so that everyone who handles a spreadsheet becomes accustomed to a standard input.

Simple statistics

Another common use for spreadsheet functions is to pull useful information out of a list, such as a series of test scores in a class, or a summary of earnings per quarter for a company.

You can, of course, scan a list of figures if you want basic information such as the highest or lowest entry or the average. The only trouble is, the longer the list, the more time you waste and the more likely you are to miss what you’re looking for. Instead, it is usually quicker and more efficient to enter a function. Such reasons explain the existence of a function like COUNT, which does no more than give the total number of entries in the designated cell range.

Similarly, to find the highest or lowest entry, you can use MIN or MAX. For each of these formulas, all arguments are either a range of cells, or a series of cells entered individually.

Each also has a related function, MINA or MAXA, which performs the same function, but treats a cell formatted for text as having a value of 0 (The same treatment of text occurs in any variation of another function that adds an "A" to the end). Either function gives the same result, and could be useful if you used a text notation to indicate, for example, if any student were absent when a test was written, and you wanted to check whether you needed to schedule a makeup exam.

For more flexibility in similar operations, you could use LARGE or SMALL, both of which add a specialized argument of rank. If the rank is 1 used with LARGE, you get the same result as you would with MAX. However, if the rank is 2, then the result is the second largest result. Similarly, a rank of 2 used with SMALL gives you the second smallest number. Both LARGE and SMALL are handy as a permanent control, since, by changing the rank argument, you can quickly scan multiple results.

You would need to be an expert to want to find the Poisson Distribution of a sample, or to find the skew or negative binominal of a distribution (and, if you are, you will find functions in Calc for such things). However, for the rest of us, there are simpler statistical functions that you can quickly learn to use.

In particular, if you need an average, you have a number to choose from. You can find the arithmetical means—that is, the result when you add all entries in a list then divided by the number of entries—by using AVERAGE, or AVERAGE A to include text entries and to give them a value of zero.

In addition, you can get several other forms of averages:

  • MEDIAN: The entry that is exactly half way between the highest and lowest number in a list.
  • MODE: The most common entry in a list of numbers.
  • QUARTILE:The entry at a set position in the array of numbers. Besides the cell range, you enter the type of Quartile: O for the lowest entry, 1 for the value of 25%, 2 for the value of 50%, 3 for 75%, and 4 for the highest entry. Note that the result for types 1 through 3 may not represent an actual item entered.
  • RANK: The position of a given entry in the entire list, measured either from top to bottom or bottom to top. You need to enter the cell address for the entry, the range of entries, and the type of rank (0 for the rank from the highest, or 1 for the rank from the bottom.

Some of these functions overlap; for example, MIN and MAX are both covered by QUARTILE. In other cases, a custom sort or filter might give much the same result. Which you use depends on your temperament and your needs. Some might prefer to use MIN and MAX because they are easy to remember, while others might prefer QUARTILE because it is more versatile.

Using these functions

In some cases, you may be able to get similar results to some of these functions by setting up a filter or a custom sort. However, in general, functions are more easily adjusted than filters or sorts, and provide a wide range of possibilities.

At times, you may just want to enter one or more formulas temporarily in a convenient blank square, and delete it once you have finished. However, if you find yourself using the same functions constantly, you should consider creating a template and including space for all the functions you use, with the cell to their left used as a label for them. Once you have created the template, you can easily update each formula as entries change, either automatically and on-the-fly or pressing the F9 key to update all selected cells.

No matter how you use these functions, you will probably find them simple to use and adaptable to many purposes. And, by the time you have mastered this handful, you will be ready to try more complex functions.

Rounding off numbers

For statistical and mathematical purposes, Calc includes a variety of ways to round off numbers. If you’re a programmer, you may also be familiar with some of these methods. However, you don’t need to be a specialist to find some of these methods useful. You may want to round off for billing purposes, or because decimal places don’t translate well into the physical world—for instance, if the parts you need come in packages of 100, then the fact you only need 66 is irrelevant to you; you need to round up for ordering. By learning the options for rounding, you can make your spreadsheets more immediately useful.

When you use a rounding function, you have two choices about how to set up your formulas. If you choose, you can nest a calculation within one of the rounding functions. For instance, the formula =ROUND((SUM(A1;A2)) adds the figures in cells A1 and A2, then rounds them off to the nearest whole number. However, even though you don’t need to work with exact figures every day, you may still want to refer to them occasionally. If that is the case, then you are probably better off separating the two functions, placing =SUM(A1;A2) in cell A3, and =ROUND (A3) in A4, and clearly labelling each function. Which choice you make for layout depends largely on your work habits.

Rounding methods

The most basic function for rounding numbers in Calc is ROUND. This function will round off a number according to the usual rules of symmetric arithmetic rounding: a decimal place of .4 or less gets rounded down, while one of .5 or more gets rounded up. However, at times, you may not want to follow these rules. For instance, if you are one of those contractors who bills a full hour for any fraction of an hour you work, you would want to always round up so you didn’t lose any money. Conversely, you might choose to round down to give a slight discount to a long-established customer. In these cases, you might prefer to use ROUNDUP or ROUNDDOWN, which, as their names suggest, round a number to the nearest integer above or below it.

All three of these functions require the single argument of number—the cell or number to be rounded. Used with only this argument, all three functions round to the nearest whole number, so that 46.5 would round to 47 with ROUND or ROUNDUP and 46 with ROUNDDOWN. However, if you use the optional count argument, you can specify the number of decimal places to include. For instance, if number was set to 1, then 48.65 would round to 48.7 with ROUND or ROUNDUP and to 48.6 with ROUNDDOWN.

As an alternative to ROUNDDOWN when working with decimals, you can use TRUNC (short for truncate). It takes exactly the same arguments as ROUNDDOWN, so which function you use is a matter of choice. If you aren't working with decimals, you might choose to use INT (short for integer), which takes only the number argument.

Another option is the ODD and EVEN pair of functions. ODD rounds up to the nearest odd number if what is entered in the number argument is a positive number, and rounds down if it is a negative number, while EVEN does the same for an even number.

Options are the CEILING and FLOOR functions. As you can guess from the names, CEILING rounds up and FLOOR rounds down. For both functions, the number that they round to is determined by the closest multiple of the number that you enter as the significance argument. For instance, if your business insurance is billed by the work week, the fact that you were only open three days one week would be irrelevant to your costs; you would still be charged for an entire week, and therefore might want to use CEILING in your monthly expenses.

Conversely, if you are building customized computers and completed 4.5 in a day, your client would only be interested in the number ready to ship, so you might use FLOOR in a report of your progress.If cell E1 contains the value 46.7, =CEILING(E1;7) will return the value 49.

Besides number and significance, both CEILING and FLOOR include an optional argument called mode, which takes a value of 0 or 1. If mode is set to 0, and both the number and the significance are negative numbers, then the result of either function is rounded up; if it is set to 1, and both the number and the significance are negative numbers, the the results are rounded down. In other words, if the number is -11 and the significance is -5, then the result is -10 when the mode is set to 0, but -15 when set to 1.

However, if you are exchanging spreadsheets between Calc and MS Excel, remember that the mode argument is not supported by Excel. If you want the answers to be consistent between the two spreadsheets, set the mode in Calc to -1.

A function somewhat similar to CEILING and FLOOR is MROUND. Like CEILING AND FLOOR, MROUND requires two arguments, although, somewhat confusingly, the second one is called multiple rather than significance, even though the two are identical. The difference between MROUND and CEILING and FLOOR is that MROUND rounds up or down using symmetric arithmetic rounding. For example, if the number is 77 and the multiple is 5, then MROUND gives a result of 75. However, if the multiple is changed to 7, then MROUND's result becomes 77.

Once you tease the various rounding functions out of Calc’s long, undifferentiated list of functions, you can start to decide which is most useful for your purposes.

However, one last point is worth mentioning: If you are working with more than two decimal places, don't be surprised if you don’t see the same number of decimal places on the spreadsheet as you do on the function wizard. If you don’t, the reason is that Tools > Options > OpenOffice.org Calc > Calculate > Decimal Places defaults to 2. Change the number of decimal places, and, if necessary, uncheck the Precision as shown box on the same page, and the spreadsheet will display as expected.


Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Personal tools