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?

There are many techniques to help you here.

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