Styles for text & numbers in same column

[Skompy]

I have some columns in a very large spreadsheet which contain both text and numbers. How do I apply a style to such columns?
My initial thought was to sort each of these columns, create and apply a style for all the cells in that column (using the format painter tool) which have text and then do the same for those rows which have numbers. Then, as I add further rows of data, I simply use the format painter to apply the appropriate styles to each row, from previous rows which have the exact same style in each column.

Also, I have one column where I express how many times an outcome was achieved, out of a total number of events which have occurred, as follows;
0/0, 0/1, 1/3, 2/4, 1/5, 0/3, etc,. etc. I have problems with 1/2, 1/4 and 3/4, as these are treated as a division calculation by Calc. So I have to add spaces as follows; 1 / 2, 1 / 4, 3 / 4. How should I set the style for this column and also, deal with the division issue?

This spreadsheet will ultimately become a database and so I would also wish to ensure any issues with cell values and styles are resolved beforehand.

[jrkrideau]

Forget things like the format painter tool. They are old and primitive.

You need to read up on Apache OpenOffice Styles.

Have a look at Introduction to Styles . It is aimed at Writer not Calc but the basic principles of using Styles is the same in Calc as Writer, there are just fewer Style types.

As for the vulgar fractions, just format them that way. As an example, type 0.5 in a cell and then go Format > Cells > Fraction.

Those current entries laid out as you are doing it will mean that it cannot be used as a numeric either in the spreadsheet or as a data base.

[Skompy]

Forget things like the format painter tool. They are old and primitive.

So, if I use the format painter on cells that have styles applied, will all the styles in the row I am copying from not be applied to the row I am copying to?

As for the vulgar fractions, just format them that way. As an example, type 0.5 in a cell and then go Format > Cells > Fraction.

So I have to change the value for each of the cells that result in the fraction calculation ( 1 / 2, 3 / 4, etc) and for the rest, by applying a style?

Those current entries laid out as you are doing it will mean that it cannot be used as a numeric either in the spreadsheet or as a data base.

I did not understand what you wrote above. Can you rephrase?

[jrkrideau]

Forget things like the format painter tool. They are old and primitive.

So, if I use the format painter on cells that have styles applied, will all the styles in the row I am copying from not be applied to the row I am copying to?

Sorry but I have no idea. I have never used it. I never format anything using the formatting toolbar. If something needs to be formatted I create/use a Style.

As for the vulgar fractions, just format them that way. As an example, type 0.5 in a cell and then go Format > Cells > Fraction.

[quoue ]So I have to change the value for each of the cells that result in the fraction calculation ( 1 / 2, 3 / 4, etc) and for the rest, by applying a style? [/quote]

Well, I put it that you need to input the values as decimal fractions and then apply a Style to format the number as a vulgar fraction. Someone with more experience in Calc may be able to suggest some way of removing the spaces and converting the result to numeric. If you already have a lot of entries like that, the effort would be worth it.

It should not too hard but I am pretty rusty in those areas so I will not suggest anything. Note, I am assuming that those cells are character cells.

Those current entries laid out as you are doing it will mean that it cannot be used as a numeric either in the spreadsheet or as a data base.

I did not understand what you wrote above. Can you rephrase?

[/quote]

Okay, this applies to my comments above about converting to numeric, as well.

If I correctly understand what you have done to get a fraction that looks like 1 / 2 in a cell, you do not have a numeric value in that cell. You have text.

Try View > Value Highlighting.
Numeric data will show up in blue and character data (i.e text) will show up in black. If I am correct, all your "vulgar fractions" such as 1 / 2 will be black and you cannot do anything mathematical with them. As an experiment try multiplying two of those cells together and see what happens.

If you are using the spreadsheet as a data base, then the data base will see those cells as character not numeric data.

You might want to have a look at Ten concepts that every Calc user should know

[jrkrideau]

Forget things like the format painter tool. They are old and primitive.

So, if I use the format painter on cells that have styles applied, will all the styles in the row I am copying from not be applied to the row I am copying to?

Sorry but I have no idea. I have never used it. I never format anything using the formatting toolbar. If something needs to be formatted I create/use a Style.

As for the vulgar fractions, just format them that way. As an example, type 0.5 in a cell and then go Format > Cells > Fraction.

[quote ]So I have to change the value for each of the cells that result in the fraction calculation ( 1 / 2, 3 / 4, etc) and for the rest, by applying a style?

Well, I put it that you need to input the values as decimal fractions and then apply a Style to format the number as a vulgar fraction. Someone with more experience in Calc may be able to suggest some way of removing the spaces and converting the result to numeric. If you already have a lot of entries like that, the effort would be worth it.

It should not too hard but I am pretty rusty in those areas so I will not suggest anything. Note, I am assuming that those cells are character cells.

Those current entries laid out as you are doing it will mean that it cannot be used as a numeric either in the spreadsheet or as a data base.

I did not understand what you wrote above. Can you rephrase?

[/quote]

Okay, this applies to my comments above about converting to numeric, as well.

If I correctly understand what you have done to get a fraction that looks like 1 / 2 in a cell, you do not have a numeric value in that cell. You have text.

Try View > Value Highlighting.
Numeric data will show up in blue and character data (i.e text) will show up in black. If I am correct, all your "vulgar fractions" such as 1 / 2 will be black and you cannot do anything mathematical with them. As an experiment try multiplying two of those cells together and see what happens.

If you are using the spreadsheet as a data base, then the data base will see those cells as character not numeric data.

You might want to have a look at Ten concepts that every Calc user should know[/quote]

[Villeroy]

1/2 enters the character ½ unless you turned off the respective option in Tools>AutoCorrect... This is particularly annoying because this auto-correction is a useful feature in Writer and the settings apply to all components.
Alternatively, you can enter 1/2{space},Ctrl+Z,{Enter}. This enters the character ½, revokes the auto-correct and enters "1/2 " with trailing space.

=1/2 is a formula which yields cell value 0.5. The leading = triggers formula evaluation of your input.
1/2 is not a formula. 1/2 is a text.
However, 1/2 (with no auto-correction) or 1/2/ enters a day number (date), which represents either this year's first of February or --with US locale-- second of January. This is numeric evaluation. 1/2 or 8/7 serves as a date shortcut, 12% enters the actual value 0.12 and the word TRUE is just another fancy way to enter the value 1 into a cell.

If you want to enter a numeric text such as "1/2" without any evaluation into a cell that is not formatted as text, you have to enter a leading apostrophe. The same apostrophe lets you enter a literal text with a leading =. The apostrophe supresses all evaluation without becoming part of the cell value.

This spreadsheet will ultimately become a database and so I would also wish to ensure any issues with cell values and styles are resolved beforehand.

So you should know if the column will be a text column or not. You can not mix types in database columns. If the column will be text, everything will be imported as text. If the database column will be decimal, you will get import errors for each text value that consists of more than digits with a decimal point. In case of some integer type you will get errors for text that does not consist of digits only. Text dates will be accepted only if they are in ISO format YYYY-MM-DD

[Skompy]

So you should know if the column will be a text column or not. You can not mix types in database columns. If the column will be text, everything will be imported as text. If the database column will be decimal, you will get import errors for each text value that consists of more than digits with a decimal point. In case of some integer type you will get errors for text that does not consist of digits only. Text dates will be accepted only if they are in ISO format YYYY-MM-DD

So how do you suggest I express the data in this column?
Just to re-iterate, this column is expressing how many times something occurred from the number of times an event took place (let's say it is how many times a horse won a race out of how many races it ran in, so 1/4, 2/5, 0/4, etc).

[Lupp]

So how do you suggest I express the data in this column?

Assuming your data are assured to be that simple (accepted by the RegEx "^[0-9]+/[1-9][0-9]*$") for a content in A1 e.g. you can use the formula

Code: Select all   Expand viewCollapse view

=VALUE("0 " & A1)

to interpret the text as a fraction and to get its value. This can replace recognition of fractions (which is a real mess). As always formatting is a different thing. AOO only has a rudimentary formatting of numbers to fractions which also not is reliable for higher denominators. For the given example the format code "?/?" should suffice. LibO V5.3 and higher supports enhanced fraction formatting. (Examples will be reduced to older standards by saving them with AOO.)

To automate the conversion in situ requires custom programming.

[Villeroy]

Completely wrong approach.
In a database (true database or database on sheets) you only store the results (points, goals, ranks, whatever).
From those figures you calculate results such as ratios of wins and losses and everything else. You never store any calculation results.
If you ever store ratios, you store 2 separate numbers, say 1 and 5 which can be easily concatenated to text "1/5" for reporting purposes.

[Lupp]

Completely wrong approach.

Of course! Or?
I didn't talk about the migration of someting having this and that in the same column to a database. I answered a question that I had understood as one concerning the conversion of text looking like fractions into numerical data. If a database finally aimed at should better contain the "number of wins" (numerator) and the "number of opportunities" (denominator) or a value of the ratio is a design decision I would not anticipate. Whoever has to decide should know, however, that he cannot get back numerator and denominator from the ratio. You can get the numerator e.g. if you stored denominator and ratio, of course (sufficiently precise arithmetic assumed). This at least formally in accordance with deprecating redundant data.

If the question was about extracting the numerator and the denominator from a text of fraction syntax, I would first blame spreadsheets for not having implemented a textsplit function, and then suggest a solution using three columns.

[Skompy]

I am now completely lost following the last 2 replies and I have no idea what I should do.

[robleyd]

In a database (true database or database on sheets) you only store the results (points, goals, ranks, whatever).
From those figures you calculate results such as ratios of wins and losses and everything else. You never store any calculation results.
If you ever store ratios, you store 2 separate numbers, say 1 and 5 which can be easily concatenated to text "1/5" for reporting purposes.

That is the essence you need to consider for converting to a database.

[Skompy]

That is the essence you need to consider for converting to a database.

I am none the wiser from what you said.

I think I need someone to use simple language/terms.
The reply from Villeroy uses the word results in two different contexts and I do not understand how to separate one from the other, or indeed what he is referring to...

"In a database (true database or database on sheets) you only store the results"
"From those figures you calculate results"

[robleyd]

In a database (true database or database on sheets) you only store the resultsoutcomes (points, goals, ranks, whatever).
From those figures you calculate results such as ratios of wins and losses and everything else. You never store any calculation results.
If you ever store ratios, you store 2 separate numbers, say 1 and 5 which can be easily concatenated to text "1/5" for reporting purposes.

Perhaps that small change will help.

[Skompy]

In a database (true database or database on sheets) you only store the resultsoutcomes (points, goals, ranks, whatever).
From those figures you calculate results such as ratios of wins and losses and everything else. You never store any calculation results.
If you ever store ratios, you store 2 separate numbers, say 1 and 5 which can be easily concatenated to text "1/5" for reporting purposes.

Perhaps that small change will help.

Well, I do not have a database at present, only a spreadsheet. There is also no more data I can enter too (the ratio is not wins/runs, its something else. I gave the example of wins/runs only as an example), not to what my ratio actually represents. I couldn't come up with the term ratio at the time of my OP.

I have several columns with data that is numeric and text and I cannot get away from that because of the nature oif the data.
In a couple of columns, there is no data to enter, so I enter "N/A". I don't know what else to put instead.

[Villeroy]

In a database (true database or database on sheets) you only store the resultsoutcomes (points, goals, ranks, whatever).
From those figures you calculate results such as ratios of wins and losses and everything else. You never store any calculation results.
If you ever store ratios, you store 2 separate numbers, say 1 and 5 which can be easily concatenated to text "1/5" for reporting purposes.

Perhaps that small change will help.

Thank you.

In a couple of columns, there is no data to enter, so I enter "N/A". I don't know what else to put instead.

This indicates bad table design.

[robleyd]

This spreadsheet will ultimately become a database and so I would also wish to ensure any issues with cell values and styles are resolved beforehand.

And in response to advice about preparing the spreadsheet data to ready it to become a database:

Well, I do not have a database at present, only a spreadsheet.

Do you no longer require advice on potentially converting to a DB? Please let us know so we don't add unnecessarily to the topic.

I have several columns with data that is numeric and text and I cannot get away from that because of the nature oif the data.
In a couple of columns, there is no data to enter, so I enter "N/A". I don't know what else to put instead.

Is the N/A the only data that is text? In any case, consider leaving those cells empty rather than adding probably meaningless data.

It might be helpful if you were to upload a sample spreadsheet showing the exact type of data you are working with, and the type of result you wish to achieve.

[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire and post the link here. The link also contains information on how to anonymise your document if it contains confidential information.

[Skompy]

Do you no longer require advice on potentially converting to a DB? Please let us know so we don't add unnecessarily to the topic.

I don't really need DB advice now, but I want to prevent the s/s from having attributes now which may cause a problem later on.

I have several columns with data that is numeric and text and I cannot get away from that because of the nature oif the data.
In a couple of columns, there is no data to enter, so I enter "N/A". I don't know what else to put instead.

Is the N/A the only data that is text? In any case, consider leaving those cells empty rather than adding probably meaningless data.

Yes, in those columns in which sometimes, no data can apply.

It might be helpful if you were to upload a sample spreadsheet showing the exact type of data you are working with, and the type of result you wish to achieve.

I have created a small sample of my s/s, with some columns taken out and some of the data changed (these changes won't make any difference to the issues I have written about). I hope the data is enough.
The columns with the issues are as follows;

E - This column can contain N/A because there is no info sometimes for this data element.
T-Y - These can have a single character of numeric value (0-9) or a single text character, as shown in the example. This indicates the horse's finishing position in its previous 6 races, or what happened if it did not finish (Pulled Up, Fell, Unseated Rider, Slipped Up, Run Out, Refused to Race).
Z - This is the ratio column that is proving tricky. How to display this ratio when 1/2 or 3/4 is required.
AD - This can have the same data as any of the T-Y columns and so has the same issue.

[Lupp]

Reworked example attached.
Explanations contained.

[Skompy]

Reworked example attached.
Explanations contained.

Thanks for your help.

For the data in columns T-Y, this has all been entered as numbers or text as appropriate. I can correct the unintended formatting issue of the @ symbol, but how do you suggest I deal with the issue of applying numeric and text styles in the same column? I cannot get away from needing to contain text and numeric values in the same column, even though the data is row specific not column specific.
Just to clarify, the data in columns T-Y do not need to be separated out into multiple columns. I just thought it would be better to hold the data that way, to avoid/lessen input errors and also, because I can always concatenate the data later on into one data string.

The content of column E is always either a numeric value or nothing at all (because a numeric value does not apply in some instances).
So when there is no data to enter for a particular row, do you suggest I enter 0.0 instead of N/A, or just leave the cell blank?

[keme]

If the columns in question, where you need numbers and text, are the "outcome range" in columns T-Y, I'd use text formatting there to force all input to be interpreted as text. I would also use data validation to force entry of only the allowed outcomes (placements, or reasons for no placement). Not strictly necessary, but will aid in keeping input sensible. Still vulnerable to format overriding, which happens inadvertently in some cases (copy/paste in particular).

How does the current setup cause trouble? Why do you need specific formatting? Do you have calculations, sorting or other operations that give wrong results?

[robleyd]

Is there any reason why what you current record as a text string might not be replaced with a number? E.g. place might be 1-9 and for non-finishers, substitute a numeric code thus:

22=Pulled Up
33=Fell
44=Unseated Rider
55=Slipped Up
66=Run Out
77=Refused to Race

or such other numbers as you prefer.

[Skompy]

If the columns in question, where you need numbers and text, are the "outcome range" in columns T-Y, I'd use text formatting there to force all input to be interpreted as text.

I don't know what consequences or implications this would have, down the road.

I would also use data validation to force entry of only the allowed outcomes (placements, or reasons for no placement). Not strictly necessary, but will aid in keeping input sensible. Still vulnerable to format overriding, which happens inadvertently in some cases (copy/paste in particular).

I do not understand/follow what you said above. I have no idea what data validation is.

How does the current setup cause trouble? Why do you need specific formatting? Do you have calculations, sorting or other operations that give wrong results?

I did have some sorting problems several weeks ago (column AE in my sample ss) and FJCC and Villeroy helped me to solve that, although the issue of text and numbers in the previous column (AD) remain. This is unavoidable because of the nature of the data (finishing position or fate).
I am not sure what other trouble there is at this point. I am just feeling that numbers being treated/formatted as text and text being treated/formatted as numbers will surely cause some problems when I want to start analyzing all the data, doing calculations, making graphs, etc., etc., etc.

[Skompy]

Is there any reason why what you current record as a text string might not be replaced with a number? E.g. place might be 1-9 and for non-finishers, substitute a numeric code thus:

22=Pulled Up
33=Fell
44=Unseated Rider
55=Slipped Up
66=Run Out
77=Refused to Race

or such other numbers as you prefer.

The above is not possible because all horse racing results are based on assigning each horse a single character for its finishing position or non positioning fate for every race (0 (zero) is used for when a horse finishes 10th or worse, with the idea being that there is no material difference between a horse which finished 10th and one which finished 18th).
So in your suggestion, 22 would look like the horse came second twice, etc.

The same issue arises for column AD, which is the current race result (Columns T-Y are previous runs) and so I have text and numbers in this column.

I have wondered whether for analytical purposes, I should somehow treat all non positional results (Fell, Unseated Rider, Pulled Up, etc) as zero for calculation, graphing, analytical purposes. However, I cannot say at this stage whether a horse which fell, for example at the 5th fence/hurdle, should be seen in the same light as a horse which finished 10th or worse and thus ranked the same.

[keme]

Building further on Lupp's work, I added data validation to the outcome columns you indicated (previous and current) and also on the Sex column.
I am no horse expert, so my suggested codes may be all wrong. Fix them in the Validitylists sheet.
Data validation is established by selecting cells to validate, then select menu item Data - Validity... The help text may get in your way, and can easily be disabled by unticking in the second tab of the validity dialog. If you keep the help text, you need to manually keep it in sync with the codeset if it needs changing.

Note that in some cells you get a selection list. If you find it useful (or not), make your preferred setting for each range by ticking/unticking the "Show selection list" item in the first tab of the validity dialog.

I also made styles for the win ratio columns added by Lupp, so numerator and denominator display more like a proper ratio. Note that the slash is part of the format, and should not be entered with the number.

For sorting, grouping and further analysis you can use a lookup to the rank/gender columns in the validity lists. This way, the "unplaced" code (zero) will be sorted after the placement codes, not before (as would be the case when sorting on the codes themselves).

If you want to distinguish the different non-completion outcomes, you can assign a different rank number to each of them. This is in practice what robleyd suggested, but without losing your codeset. I guess you should always keep the non-completion ranks higher than the Unplaced rank.

It may make sense to add a "personnel" validity list where you keep your trainers and jockeys/drivers, and perhaps also other personnel who could influence the horses' performance. The validity lists are useful for consistent entry, and they can serve as a foundation for further analysis.

Note that a spreadsheet is a fragile framework. You have the freedom to build complex analysis models, but also the freedom to make mistakes. Any time you move or cut/paste cell content, you run the risk of defeating the validity checks/formatting, and your analysis formulas may also be disturbed. Manual input is reasonably safe.

A database setup will be more rigid. More difficult to do what you want, perhaps, but also more difficult to make mistakes, because you are forced to think about data structure and data types from the outset. My advice: If you plan to use this on a large scale, and/or there will be other persons using the system, convert to a proper database as soon as you can!

[Lupp]

(Just a remark.)
Concerning the columns T throught Y I didn't worry about the type at all. Whether there shows up a letter or a digit, it's all the same just a code for some information only of meaning in the context of horse-racing. Probably the digits stand for a placement in a race. So what? It's an atom of information. That's all. As long as I don't know what "F" and "P" stand for I have not even a clue to guess what probably might be intended if it comes to an evaluation. Why are there 6 columns and not 4 or 27? It's arbitrary, and surely only of any meaning for horse-race adicts or those making their money in this field.

In fact I would not expect that this kind of data collection can ever result in a reasonable database. If I want to have information in the field of racing horses and horse races, I need to keep some tables usable for lookup about the horses, the courses, the jockeys or whatever. These tables must NOT contain information that can change every other day like recent results in races.
Interested in races and in betting probably, I need a table of race results where any horse, jockey, course, date, place, ... is either one very simple direct datum of one specific type or something represented by a foreign key pointing into one of the previously mentioned tables.

Everyting else needs to be delegated to reports, indexes, some SQL stuff generally, imo. Am I wrong?

Some months ago I expended some time on the collation of poker hands though not at all interested in poker, because it was requested in a forum. This was rather challenging, but also interesting due to its specific difficulties. To evaluate results of baseball games given in a extremely stubborn scheme (defined without any concern in rational points of view, just as tradition had shaped it) for a completely useless question only of interest for betters probably was a different thing. It was mainly boring, and I wouldn't do it anothher time. The current topic may be of the second class.

[Skompy]

Building further on Lupp's work, I added data validation to the outcome columns you indicated (previous and current) and also on the Sex column.
I am no horse expert, so my suggested codes may be all wrong. Fix them in the Validitylists sheet.
Data validation is established by selecting cells to validate, then select menu item Data - Validity... The help text may get in your way, and can easily be disabled by unticking in the second tab of the validity dialog. If you keep the help text, you need to manually keep it in sync with the codeset if it needs changing.

Note that in some cells you get a selection list. If you find it useful (or not), make your preferred setting for each range by ticking/unticking the "Show selection list" item in the first tab of the validity dialog.

I also made styles for the win ratio columns added by Lupp, so numerator and denominator display more like a proper ratio. Note that the slash is part of the format, and should not be entered with the number.

For sorting, grouping and further analysis you can use a lookup to the rank/gender columns in the validity lists. This way, the "unplaced" code (zero) will be sorted after the placement codes, not before (as would be the case when sorting on the codes themselves).

If you want to distinguish the different non-completion outcomes, you can assign a different rank number to each of them. This is in practice what robleyd suggested, but without losing your codeset. I guess you should always keep the non-completion ranks higher than the Unplaced rank.

It may make sense to add a "personnel" validity list where you keep your trainers and jockeys/drivers, and perhaps also other personnel who could influence the horses' performance. The validity lists are useful for consistent entry, and they can serve as a foundation for further analysis.

Note that a spreadsheet is a fragile framework. You have the freedom to build complex analysis models, but also the freedom to make mistakes. Any time you move or cut/paste cell content, you run the risk of defeating the validity checks/formatting, and your analysis formulas may also be disturbed. Manual input is reasonably safe.

A database setup will be more rigid. More difficult to do what you want, perhaps, but also more difficult to make mistakes, because you are forced to think about data structure and data types from the outset. My advice: If you plan to use this on a large scale, and/or there will be other persons using the system, convert to a proper database as soon as you can!

I am bamboozled by your post and I cannot understand almost all of it. I find it difficult to learn new things in this way (text on a screen) and I still have no idea about this validity thing or the other things you have written about.

[Skompy]

(Just a remark.)

In fact I would not expect that this kind of data collection can ever result in a reasonable database. If I want to have information in the field of racing horses and horse races, I need to keep some tables usable for lookup about the horses, the courses, the jockeys or whatever. These tables must NOT contain information that can change every other day like recent results in races.
Interested in races and in betting probably, I need a table of race results where any horse, jockey, course, date, place, ... is either one very simple direct datum of one specific type or something represented by a foreign key pointing into one of the previously mentioned tables.

Everyting else needs to be delegated to reports, indexes, some SQL stuff generally, imo. Am I wrong?

I have no idea if you are wrong nor also whether the data I have collected in my s/s will produce worthwhile results or not. The data elements have been selected on my own ideas and knowledge with horse racing. The sample spreadsheet which I attached also excludes some columns.
I have long since realized that the way I have arranged the data may not be correct, but this does not concern me since I believe I can re-arrange it all differently by breaking it up into separate tables for a database, once I have all the data collected. Is this approach wrong?

I also have no experience, at all with databases. I will have to learn it all from scratch.

[keme]

... I find it difficult to learn new things in this way (text on a screen) and I still have no idea about this validity thing or the other things you have written about.

I know. That is why I attached a reworked version of your file (which should be familiar to you), explained what I have done to it (added data validation mechanisms mostly) and where you can find the tools and parts (select one of the sex or outcome cells to see how data validation behaves, then select menu item Data - Validity... to inspect and edit the various settings).

To achieve sensible sorting and evaluation, and also more advanced analysis, you need to use lookup functions to translate codes into numbers. The validation is a required first step to ensure consistent lookup values.

I apologize for my previous somewhat bloated post.Please explain what kind of analysis you need, and you will receive further advice.

[Skompy]

... I find it difficult to learn new things in this way (text on a screen) and I still have no idea about this validity thing or the other things you have written about.

I know. That is why I attached a reworked version of your file (which should be familiar to you), explained what I have done to it (added data validation mechanisms mostly) and where you can find the tools and parts (select one of the sex or outcome cells to see how data validation behaves, then select menu item Data - Validity... to inspect and edit the various settings).

To achieve sensible sorting and evaluation, and also more advanced analysis, you need to use lookup functions to translate codes into numbers. The validation is a required first step to ensure consistent lookup values..

I still do not understand what you have done or why. For the previous runs columns (T-Y), I see you have added a yellow sticky-note like box with instructions inside it - what is this for? I don't need instructions on what data to enter, anywhere. All the codes you have entered in this yellow box (0-9 plus all the single letter codes) are correct, but again, this is not useful for me, so I am puzzled as to its purpose.
I see also you have provided another yellow box for the sex column (gender codes) (one of these codes is wrong but that is not so important right now) but again, I do not understand what this is for, or indeed how you created it. I cannot find any explanations of what you have done.

I cannot follow what you are trying to do or why/its purpose. And when I select Validity from the Calc menu, I see a dialog box appear but I have no idea what to do next. Do you know if there is a good instructional step-by-step video on Youtube for this?

I apologize for my previous somewhat bloated post.Please explain what kind of analysis you need, and you will receive further advice.

Its not bloated to me, its just that I cannot understand hardly anything that you wrote and so I cannot follow it. I usually need a live person to teach me complex things, especially when those things are abstract like this. Text on a screen does not usually work for me in such situations.

I do not yet know what kind of analysis I will need, once the data is complete and this goal is a long way away at this time.
Also, I might not be able to tell you what I need without also giving you (or anyone else) confidential and key information.