Spreadsheets are good for collecting data like temperatures, stock prices, and sports data. However, the data on its own does us little good. We need ways to analyze the data. LibreOffice Calc provides us with built-in functions to do this. In this article, we will look at a few of the simple statistical functions built into Calc. This is not meant as a thorough examination of all the functions, but more an exercise to get you comfortable using functions. Importing the Data For our data, I have collected a few months worth of stock prices on the mutual fund FFACX. You can find the data at http://pastebin.com/AeLcyM1t. The data is laid out as comma-delimited plain text. Copy the data from Pastebin. Make sure that you copy from the ”Raw Paste Data” text box. Create a new Calc document. Right-click on cell A1 and select Paste. The Text Import dialog will show. Under Separator Options, select “Separated by” and check Comma. Click OK. You now have two columns of data, the date and the price. Save the sheet.

Les tableurs sont parfaits pour collecter des données comme les températures, les cours des actions, et les données sportives. Toutefois, les données en elles-mêmes sont de peu d'intérêt. Nous devons trouver des moyens pour les analyser. LibreOffice Calc fournit des fonctions intégrées pour cela. Dans cet article, nous allons examiner quelques-unes des fonctions statistiques simples intégrées dans Calc. Nous n'allons pas faire un examen approfondi de toutes les fonctions, mais plutôt s'exercer pour que vous soyez plus à l'aise pour utiliser ces fonctions.

Importer les données

Pour nos données, j'ai rassemblé la valeur du prix d'achat d'actions sur le fonds mutualisé FFACX sur quelques mois. Vous pouvez trouver les données sur http://pastebin.com/AeLcyM1t. Les données sont sous forme de texte délimité par des virgules. Copiez les données depuis Pastebin. Assurez-vous que vous copiez à partir de la zone de texte des « données brutes » (ou « Raw Paste Data »). Créez un nouveau document Calc. Faites un clic droit sur la cellule A1 et sélectionnez Coller. La boîte de dialogue d'importation de texte va apparaître. Sous Options de séparateur, sélectionnez « Séparé par » et cochez « Virgule ». Cliquez sur OK. Vous avez maintenant deux colonnes de données, la date et le prix. Enregistrez la feuille.

[Ndt] Si Calc est configuré en langue française, il faut remplacer tous les « . » par des « , » dans la colonne B pour que les données soient reconnues comme des nombres.

Creating a Named Cell Range Throughout this article we will use the prices as our data range. Since we will use the range several times, we will name it to make the references easier. Select all the prices (cells B2:B82). The easiest way to select all the cells is to type the range B2:B82 into the cell name box on the formula toolbar and press Enter on the keyboard. Insert > Names > Define, and the “Define Name” dialog shows. Name the range “Prices”. Notice the absolute reference in the Range text box ($Sheet1.$B$2:$B$82). Names apply to a specific group of cells, so the reference is absolute. Under the Scope, we will restrict the use of this name to just Sheet1. Click Add to create the range name. Now, in any formula or function where we use the name “Prices”, it is the same as typing the cell range $Sheet1.$B$2:$B$82.

Créer une plage nommée de cellules

Tout au long de cet article, nous allons utiliser ces prix comme plage de données. Puisque nous allons utiliser cette plage à plusieurs reprises, nous allons la nommer pour rendre les références plus simples. Sélectionnez tous les prix (les cellules B2:B82). La meilleure façon de sélectionner toutes les cellules est de taper la plage B2:B82 dans la « Zone de nom » de la barre de formule et d'appuyer sur Entrée sur le clavier. Insertion > Noms > Définir, et la boite de dialogue « Définir un nom » apparaît. Nommez la zone « Prix ». Remarquez la référence absolue dans la zone de texte de la plage ($Feuille1.$B$2:$B$82). Les noms s'appliquent à un groupe spécifique de cellules, et la référence est donc absolue. Dans le champ « Étendue », nous allons restreindre l'utilisation de ce nom à Feuille1 seulement. Cliquez sur Ajouter pour créer le nom de plage.

Maintenant, dans une formule ou une fonction où nous utiliserons le nom « Prix », ce sera la même chose que d'utiliser la plage de cellules $Feuille1.$B$2:$B$82.

MAX and MIN The first two statistical functions we will look at are MAX and MIN. Both functions accept a list of values or range(s). MAX returns the maximum (or largest) value in the list. MIN does just the opposite, returning the minimum (or smallest) value in the list. Move your cursor to D4. Type in “Highest.” In the cell E4, type in the formula =MAX(Prices) You should get the result 14.16. If you look through the list of prices, you will discover this is the largest number in the list.

MAX et MIN

Les deux premières fonctions statistiques que nous examinerons sont MAX et MIN. Les deux fonctions acceptent une liste de valeurs ou plage(s). MAX retourne le maximum (la plus grande valeur) dans la liste. MIN fait exactement le contraire, retournant le minimum (la plus petite valeur) dans la liste.

Déplacez votre curseur sur D4. Saisissez « maximum ». Dans la cellule E4, tapez la formule

=MAX(Prix)

Vous devriez obtenir le résultat 14,16. Si vous regardez la liste des prix, vous découvrirez que c'est le plus grand nombre dans la liste.

In D5 put “Lowest.” The formula for E5 is =MIN(Prices) The result is 13.57, the lowest number in the list. It is also possible to use these, or any Calc function, as an operand in a formula. For example, if we wanted to know the difference between the highest and lowest price, we could use the formula =MAX(Prices) – MIN(Prices) in cell E6. In the formula, the results of the functions are calculated first. MAX(Prices) becomes 14.16, and MIN(Prices) becomes 13.57. Then the result of 14.16-13.57 is placed in the cell E6. Place the text “Difference” in the cell D6 as a label.

AVERAGE, MEDIAN, and MODE In statistics there are many ways to determine just what is the typical value for a set of numbers. Among these are the arithmetic average, the median, and the mode. The arithmetic average, known to most people as simply the average, is the sum of a series of numbers divided by the number of items in the series. In Calc we use the AVERAGE function to get the arithmetic average. The median ranks the numbers from the lowest to the highest. If the number of items in the series is an odd number, it takes the one in the middle. If it is even, the median is the arithmetic average of the two center numbers. Calc uses the MEDIAN function to calculate the median for you. Mode is the number that repeats most often. If there is a tie, it uses the smallest number. Calc uses the MODE function to get the mode for you.

We can see the results of these three functions by putting the following formulas in the cells E7, E8, and E9. =AVERAGE(Prices) =MEDIAN(Prices) =MODE(Prices) You will notice that the results are within a few hundredths of each others. This is not true in all cases. Numbers that are much larger or much smaller than all the others can affect the average. In those cases, the mode or median might better suit your needs for a typical value. The Calc statistical functions help us to analyze the data in a Calc spreadsheet. We touched on only a few of the statistical functions. Calc has over 70 statistical functions. This is just one of many categories of functions available to us in Calc. You can get a list of all the Calc functions in the help documentation.

Conditional Formatting In our data set, the top and bottom are pretty close together, so the average will work good for us as the typical value for this data set. We can use conditional formatting to mark each of the prices as either above or below the average. Start by selecting all the prices in the B column (B2:B82). Since we named the range B2:B82, a quick way to select all the prices is by using the drop down arrow for the cell name box on the formula toolbar and selecting the name “Prices(Sheet1).” Format > Conditional Formatting > Condition will bring up the Conditional Formatting dialog. Select “Cell value is” from the left drop-down list. From the drop-down list in the center, select “less than.” Enter $E$7 in the text box to the right. If you want to use the median or mode for the comparison, you can use $E$8 or $E$9. We are using an absolute reference here because we want to reference the same cell regardless of what row we are formatting.

For the Apply Style, select “New Style” from the drop-down list. The Cell Style dialog will open. On the Organizer tab, name the style “Below Average.” Switch to the Background tab and select the Red color swatch. Click OK to close the dialog and save the new style. You will see “Below Average” is now selected as the Apply Style. Click Add to add a second condition. This time select “greater than” instead of “less than.” Use the same absolute reference, $E$7, $E$8, or $E$9, as you used in the less than condition. Once again select “New Style” for the Apply Style. Give the new style the name “Above Average”, and make the background green instead of red. OK to save the new style. OK in the Conditional Formatting dialog will save the settings. Prices that are over the average will highlight in green, and the prices below the average will highlight in red. NOTE: You can create your style for conditional formatting before creating the condition. In such a case, you would just select the style to use.

I hope this article has helped you to understand the use of the statistical functions demonstrated and functions in general. We saw how to use named ranges to identify cell ranges being used repeatedly. We used functions by themselves and as operands in a formula. Using conditional formatting, we highlighted certain cells to show visually the status of the cell's value. This eliminates the need to scroll back up the sheet to check the average value. Until next month, look up some functions in the help and put them to work for you. Also, play with some of the features of the conditional formatting and see what other visuals you can create. Next time, I will show you how to validate cell values.