Styles for text & numbers in same column
Styles for text & numbers in same column
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.
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.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Styles for text & numbers in same column
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.
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.
LibreOffice 7.3.7. 2; Ubuntu 22.04
Re: Styles for text & numbers in same column
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?jrkrideau wrote:Forget things like the format painter tool. They are old and primitive.
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?jrkrideau wrote: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.
I did not understand what you wrote above. Can you rephrase?jrkrideau wrote: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.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Styles for text & numbers in same column
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.Skompy wrote: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?jrkrideau wrote:Forget things like the format painter tool. They are old and primitive.
[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]jrkrideau wrote: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.
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.
[/quote]I did not understand what you wrote above. Can you rephrase?jrkrideau wrote: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.
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
LibreOffice 7.3.7. 2; Ubuntu 22.04
Re: Styles for text & numbers in same column
Okay, this applies to my comments above about converting to numeric, as well.jrkrideau wrote: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.Skompy wrote: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?jrkrideau wrote:Forget things like the format painter tool. They are old and primitive.
jrkrideau wrote: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.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.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?
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.
I did not understand what you wrote above. Can you rephrase?jrkrideau wrote: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.
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]
LibreOffice 7.3.7. 2; Ubuntu 22.04
Re: Styles for text & numbers in same column
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.
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.
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-DDThis spreadsheet will ultimately become a database and so I would also wish to ensure any issues with cell values and styles are resolved beforehand.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Styles for text & numbers in same column
So how do you suggest I express the data in this column?Villeroy wrote: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
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).
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Styles for text & numbers in same 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 formulaSkompy wrote:So how do you suggest I express the data in this column?
Code: Select all
=VALUE("0 " & A1)
To automate the conversion in situ requires custom programming.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Styles for text & numbers in same column
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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Styles for text & numbers in same column
Of course! Or?Villeroy wrote:Completely wrong approach.
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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Styles for text & numbers in same column
I am now completely lost following the last 2 replies and I have no idea what I should do.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Styles for text & numbers in same column
That is the essence you need to consider for converting to a database.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.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Styles for text & numbers in same column
I am none the wiser from what you said.robleyd wrote:That is the essence you need to consider for converting to a database.
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"
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Styles for text & numbers in same column
Perhaps that small change will help.In a database (true database or database on sheets) you only store theresultsoutcomes (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.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Styles for text & numbers in same column
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.robleyd wrote:Perhaps that small change will help.In a database (true database or database on sheets) you only store theresultsoutcomes (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.
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.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Styles for text & numbers in same column
Thank you.robleyd wrote:Perhaps that small change will help.In a database (true database or database on sheets) you only store theresultsoutcomes (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.
This indicates bad table design.Skompy wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Styles for text & numbers in same column
And in response to advice about preparing the spreadsheet data to ready it to become a database:Skompy wrote: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.
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.Skompy wrote:Well, I do not have a database at present, only a spreadsheet.
Is the N/A the only data that is text? In any case, consider leaving those cells empty rather than adding probably meaningless data.Skompy wrote: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.
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.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Styles for text & numbers in same column
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.robleyd wrote: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.
Skompy wrote: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.
Yes, in those columns in which sometimes, no data can apply.robleyd wrote:Is the N/A the only data that is text? In any case, consider leaving those cells empty rather than adding probably meaningless data.
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.robleyd wrote: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.
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.
- Attachments
-
- sample-hr-ss.ods
- (17.51 KiB) Downloaded 252 times
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Styles for text & numbers in same column
Reworked example attached.
Explanations contained.
Explanations contained.
- Attachments
-
- aoo95451_HorseRace_1.ods
- (39.8 KiB) Downloaded 275 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Styles for text & numbers in same column
Thanks for your help.Lupp wrote:Reworked example attached.
Explanations contained.
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?
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Styles for text & numbers in same column
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?
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?
Re: Styles for text & numbers in same column
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 Raceor such other numbers as you prefer.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Styles for text & numbers in same column
I don't know what consequences or implications this would have, down the road.keme wrote: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 do not understand/follow what you said above. I have no idea what data validation is.keme wrote: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 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).keme wrote: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 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.
Last edited by Skompy on Wed Oct 17, 2018 1:53 am, edited 2 times in total.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Styles for text & numbers in same column
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).robleyd wrote: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 Raceor such other numbers as you prefer.
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.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Styles for text & numbers in same column
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 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!
- Attachments
-
- DayAtTheRaces.ods
- (39.71 KiB) Downloaded 234 times
Re: Styles for text & numbers in same column
(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.
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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Styles for text & numbers in same column
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.keme wrote: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!
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Styles for text & numbers in same column
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.Lupp wrote:(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 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.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Styles for text & numbers in same column
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).Skompy wrote:... 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.
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.
Re: Styles for text & numbers in same column
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.keme wrote: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).Skompy wrote:... 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.
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 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?
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.keme wrote:I apologize for my previous somewhat bloated post.Please explain what kind of analysis you need, and you will receive further advice.
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.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.