2016 Archives by date, by thread · List index

# Re: [libreoffice-users] how to use coding and labelling in a spreadsheet

```At 07:15 24/03/2016 +0000, Som Noname wrote:
```
calc is a powerful spreadsheet program and i want to use this for data analysis.
```
It may be more powerful than you think!

```
let's say, one study has a variable named "malnutrition" measuring the nutritional status of under-five children. this variable has two value levels - "1=malnutrition" and "2=normal"
```
```
If "normal" means *not* malnourished, surely the category should be "nutrition", not "malnutrition", and the possible value needs to be "malnourished", not "malnutrition" (as well as "normal")?
```
```
present scenario: if data in entered as "1"/"2", spreadsheet will show it as "1"/"2".
```
```
Why do people think all data needs to be represented as numbers? The numbers 1 and 2 have two properties that are inappropriate here. First there is a collating order: 1 comes before 2 - but there is no sense in which "malnourished" comes first in preference to "normal". Second, numbers have value: 2 is exactly twice the size of 1 - but there is no sense in which "normal" is exactly (or only) twice as good as "malnourished". If you see a malnourished child, you don't remark "Oh, look how 1 he is"! These are what are called non-parametric statistics, and you should avoid complication and confusion by ascribing numbers to them. (Don't believe any mathematicians who tell you everything has to be thought of in terms of numbers.)
```
```
```if it is entered as "malnutrition"/"normal", it will be shown in that manner.
```
```
```
You are lucky that - unlike in mathematics - spreadsheets are happy handling text values such as these. So using values such as "malnourished" and "normal" - or perhaps just "m" and "n" - will suit your purpose better than 1 and 2. It would be a very simple matter to make mistakes by confusing which condition was 1 and which was 2 - something much less likely with meaningful labels such as "m" and "n".
```
```
possibilities: renaming the cell. [...] cell A2 has a code of "1" and is renamed to "malnutrition" (upper left corner).
```
```
No, you misunderstand what names are about. You give a name to a *cell* (or, generally, a cell range), so the name becomes an alternative way of identifying the cell (or range) and has nothing to do with the value or values contained. And you obviously cannot name more than one cell with the same name!
```
```
my question finally comes to this - is there any way to do this in a spreadsheet program?
```

```
o If you wanted to, you could format relevant columns to expand "m" to "malnourished" and so on - as has already been explained.
```
```
o Alternatively, it would be a simple job to create a parallel column with formulae which would show the full word derived from the single input letter. You could exclude your input column from any print range to suppress its output.
```
```
o If you use the full words, as soon as you have entered the two values at least once each somewhere in a column, just typing the first letter (since they are conveniently different) will automatically show the full word as a suggestion. You need only press Enter or down-arrow or click the green arrow to complete the full word as the entry.
```
```
o Again, once both values appear somewhere in a column, you can press Ctrl+D to show a selection list. The arrow keys and the Enter key enable you to select the required value quickly.
```
```
o Best of all, you should consider using Data | Validity... to restrict values in the column to those listed in a cell range set up elsewhere in the spreadsheet. Now any selected cell comes with a drop-down menu of the permitted values and the arrangement ensures that only recognised values can be entered. Apart from the convenience, this prevents the possibility of spelling mistakes in input - which would otherwise be quite likely in a large set of data.
```
I trust this helps.

Brian Barker

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
```