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.
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.
Skompy wrote:jrkrideau wrote: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?
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.
[/quote]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.
I did not understand what you wrote above. Can you rephrase?
jrkrideau wrote:Skompy wrote:jrkrideau wrote: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.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.
[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?
[/quote]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.
I did not understand what you wrote above. Can you rephrase?
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.
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
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?
=VALUE("0 " & A1)
Of course! Or?Villeroy wrote: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.
robleyd wrote: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 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.
robleyd wrote: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.
Perhaps that small change will help.
robleyd wrote: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.
Perhaps that small change will help.
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.
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.
Skompy wrote:Well, I do not have a database at present, only a spreadsheet.
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.
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.
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.
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.
Lupp wrote:Reworked example attached.
Explanations contained.
22=Pulled Up
33=Fell
44=Unseated Rider
55=Slipped Up
66=Run Out
77=Refused to Race
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.
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).
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?
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 Race
or such other numbers as you prefer.
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!
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?
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.
keme wrote: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.
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..
keme wrote:I apologize for my previous somewhat bloated post.Please explain what kind of analysis you need, and you will receive further advice.
Users browsing this forum: Google [Bot] and 27 guests