1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Sharing spreadsheets for anybody interested.

Discussion in 'Horse Training' started by Horse Breeder Ralphy, Mar 16, 2017.

  1. Solest

    Joined:
    Dec 7, 2015
    Messages:
    83
    Likes Received:
    245
    I made a google spreadsheet version of my skill tracking document in case anyone is interested in tracking their horse's skills and for easier tracking or courser status.
    The template document can be copied from here: https://docs.google.com/spreadsheets/d/1nmq5d7yUIJ6sPysgMLS6gQtEMd_s43B2p_-2CzhjQBw/edit?usp=sharing

    Important info on how to use this spreadsheet:
    • Column E (the dark grey column) is an equation which auto counts the number of skills you have listed for a horse but will only count a value as a skill if the cell is a number 1-30, or contains the text "Yes", or "First"
    • Column D (the light grey column) is an equation which counts down the number of courser skills still needed for a horse of a particular tier to become a courser.
    • If you edit any part of column E or D you will break the equations for the entire column - there's a popup warning which you have to confirm in order to edit it.
    • The equations in columns E and D are infinite and will automatically apply to newly inserted rows.
    • There are a number of hidden columns which are crucial to the functionality of the above columns as well as a few other features, editing them will break a number of things - there's also a warning on these when making edits.
    • Column F needs to be manually entered and is used for your own confirmation to make sure that the number of skills listed in the sheet match the number of skills reported in the game.
    • "First" refers to born skills.
    • You can use "Yes" to indicate a learned skill or input a number if you know the level that the skill was learned at.
    • The default value for normal skills is "No" and for S: skills is "N/A" up to row 500. If you have more than that number of horses then you are amazing and also on your own.
    • Cell colors for cells containing "Yes", "First", "N/A", and numbers are automatically applied.
    • A gold color is automatically applied to the cell of the horses name when they gain the skills required for courser status for their specified tier. The same limitations as those for the auto-skill column (listed in bullet point 1) apply here.
    • If a horse is level 30 and not a courser their name cell is automatically changed to silver.
    • Two horses have been included as examples.
     
    #21 Solest, Mar 23, 2017
    Last edited by a moderator: Mar 27, 2017
  2. LadyRhapsody

    Joined:
    Oct 14, 2016
    Messages:
    74
    Likes Received:
    177
    This is golden. I keep track of skills in my breeding spreadsheet, but it goes by initials. C for Caution, Ch for charge and so on. Thank you for the share!
     
  3. Lady Minuit

    Lady Minuit DeMinuit Kunoichi 61 NA

    Joined:
    Jul 20, 2016
    Messages:
    1,411
    Likes Received:
    2,804
    Maybe make a master spreadsheet which would include breeding results and skill tracking? Awesome work btw!
     
  4. Rinel

    Rinel Wizard 60 EU

    Joined:
    Nov 10, 2015
    Messages:
    198
    Likes Received:
    474
    I have it and its too much info tbh, I have to scroll to see skills =\
    [​IMG]
     
  5. Rinel

    Rinel Wizard 60 EU

    Joined:
    Nov 10, 2015
    Messages:
    198
    Likes Received:
    474
    Imho its easier to use COUNTIF for "Yes", "First" and numbers
    Edited: smth like this =SUM(COUNTIF(F3:Y3;"Yes");COUNTIF(F3:Y3;"First");COUNTIF(F3:Y3;">0"))
     
    #25 Rinel, Mar 23, 2017
    Last edited by a moderator: Mar 23, 2017
  6. Lady Minuit

    Lady Minuit DeMinuit Kunoichi 61 NA

    Joined:
    Jul 20, 2016
    Messages:
    1,411
    Likes Received:
    2,804
    Wow crazy!
    But maybe use separate tabs for skills?
    This looks amazing though!
     
  7. Rinel

    Rinel Wizard 60 EU

    Joined:
    Nov 10, 2015
    Messages:
    198
    Likes Received:
    474
    I use filters alot, so I have to keep all info inside one tab ¯\_(ツ)_/¯
     
  8. Solest

    Joined:
    Dec 7, 2015
    Messages:
    83
    Likes Received:
    245
    In my personal version in excel I use COUNTIF (specifically =COUNTIF(F62:Y62,"yes")+COUNT(F62:Y62)+COUNTIF(F62:Y62,"First"))but in Google Spreadsheets I prefer to use array formulas so you can insert rows and such without having to constantly reapply all the equations. Because there are so many different little pieces to some of the functions I added, using an array makes it so you dont have to worry about messing with any of them, they auto populate (so even people with no experience in excel can get by). Array also allows you to have a single equation for an entire column rather than an equation in each cell of the column. Unfortunately there are a number of commands which break arrays (COUNTIF being one of those). It does make for a bit of an excessive work around in some instances but at least I had a bit of fun with it, and hopefully it will make things simpler for others.
     
    4 people like this.
  9. Horse Breeder Ralphy

    Joined:
    Mar 15, 2017
    Messages:
    871
    Likes Received:
    883
    You could add a new sheet to the spreadsheet document that automatically populates with all your foals as you enter them into your main breeding data sheet. Then put the skills and stats on that sheet. Basically a 'Horse Inventory/History' sheet separate from your breeding data sheet. Then you could cut out the stats and skills from your breeding data sheet as it's preserved and easier to view on the new sheet. Making each easy to view without much in the way of scrolling.
     
    2 people like this.
  10. Lady Minuit

    Lady Minuit DeMinuit Kunoichi 61 NA

    Joined:
    Jul 20, 2016
    Messages:
    1,411
    Likes Received:
    2,804
    Horse breeders: experts at all horse things, And spreadsheets.
     
  11. Rinel

    Rinel Wizard 60 EU

    Joined:
    Nov 10, 2015
    Messages:
    198
    Likes Received:
    474
    to get more tabs? hell no xD I started with 1 tab and I have 8 atm, I dont wanna more=( In case I see a horse and I wanna check its skills I just press shift and scroll, dont need to search this horse in another tab.
     
  12. Horse Breeder Ralphy

    Joined:
    Mar 15, 2017
    Messages:
    871
    Likes Received:
    883
    Yeah, that's one of the issues I have trying to decide between Excel and Google Sheets. Each have their advantages. I took a look at your spreadsheet and that formula in Column D must have been a nightmare for you to put together. My first instinct was to tackle the Google Sheets system and make it 1/20th the size (if not no more than a simple formula), but I've got enough on my hands at the moment, and after a cursory attempt kinda threw up my hands. I'm sure it can be done. But I'm in no mood at the moment (dismantling my gaming machine and upgrading hardware, and I've already made 3 trips back and forth to Microcenter for parts... gonna plow through that before I take another gander).

    Gotta say though... good lord, you must have practiced a modicum of patience putting that formula together. :confused:
     
  13. Horse Breeder Ralphy

    Joined:
    Mar 15, 2017
    Messages:
    871
    Likes Received:
    883
    No worries. To each their own. Everybody has their own preferred system of organization. :) Personally, I embrace multiple tabs. But that's cuz if I'm looking for a specific horse's skills/details, I don't want to search through the foals column to find that specific horse. But hey, everybody has their own filing system. But at the same time I pity the fool who tries to find anything on my computer desk... it's such chaos it isn't funny. :D

    [caveat] The following image is a gag. Not to be taken offensively.So please take it in the tongue-and-cheek manner that it was intended. =) (and honestly... dunno whether you're a boy or a girl)

    [​IMG]
     
    4 people like this.
  14. Solest

    Joined:
    Dec 7, 2015
    Messages:
    83
    Likes Received:
    245
    If you think that equation was rough you should take a look at what I put together to get the conditional formatting for the gold courser color to apply correctly for each tier. There's 16 rows solely dedicated to making it work and 24 individual formulas.

    To be honest, I find the process quite fun. It's kind of like a puzzle game to me. If you have any grand ideas you would like to try to implement via formulas I'd be more than happy to help you brainstorm possible ways to implement them. That goes for anyone else and their spreadsheets as well.
     
    2 people like this.
  15. Horse Breeder Ralphy

    Joined:
    Mar 15, 2017
    Messages:
    871
    Likes Received:
    883
    Just an update to a critical (and embarrassing) error I found in my spreadsheet I posted in the OP (I fixed it a few days ago, but forgot to post a followup reply). On the "Breeding Statistics" sheet some of the formulas were aggregating data from row 4 of the "Breeding Results" sheet, instead of row 3 (row 3 is the control and non-editable row). So if you're like me and typically "Insert 1 above" to keep your most recent breeds at the top, then the "Breeding Statistics" sheet would change Row 4 to Row 5 etc.. and keep incrementing it every time you inserted a new row at the top.

    If anybody is indeed using my spreadsheet, and was inserting new breeds at the top via the "Insert 1 Above" method, you should be able to open the originally shared (but now updated) spreadsheet in my original post, right click on the "Breeding Statistics" sheet tab (or click on the down arrow on the sheet tab), select "copy to", then choose your Google Sheets document that you're using. From there it's pretty simple to see how to remove the old "Breeding Statistics" sheet, and rename your new sheet tab to remove the "copy" identifier. Doing this should prevent you from having to start over and importing all your data again.

    If you've simply been adding new entries to the bottom, it shouldn't affect you. But I'd still recommend updating by the methods described above.

    My apologies for the inconvenience.

    Here's a screenshot of the updated version of the statistics sheet (new items added in addition to the critical error fix)...

    [​IMG]
     
    #35 Horse Breeder Ralphy, Mar 27, 2017
    Last edited by a moderator: Mar 27, 2017
    2 people like this.
  16. Solest

    Joined:
    Dec 7, 2015
    Messages:
    83
    Likes Received:
    245
    I updated my template to include a column which counts down the number of courser skills that still need to be gained for a particular horse to become a courser.
    If anyone has any suggestions for things they would like to see added to the template let me know.
     
    2 people like this.
  17. Sad Panda

    Joined:
    Mar 11, 2017
    Messages:
    391
    Likes Received:
    922
    Nice addition with the pie charts! I'm sure all the numbers might've been slightly off-putting for some and this adds great visuals.
    Just noticed you added a credit for me, thanks <3 But like you said, inspiration; you've made it your own
     
    #37 Sad Panda, Mar 28, 2017
    Last edited by a moderator: Mar 28, 2017
  18. Nimbus

    Nimbus Nhimbus Witch EU

    Joined:
    Mar 2, 2016
    Messages:
    33
    Likes Received:
    108
    I think there might be a minor mistake in some data-collecting thingy-majiffy on the Breeding Statistics sheet @Horse Breeder Ralphy. After entering my breeding data into the Breeding Results Shet the Breeding Results by Range/Tier/Gender box claims I have one T7 male from a range 16 breeding - that can't be, since I don't have any breedings done in that range. I've double and triple checked my info for mistakes that might have caused this - but I can't find it. It's also the only place where that mysterious T7 male shows up. Breeding Results by Tier and Breeding Percentages By Range/Tier/Gender shows it all as it should be. I would upload a picture because it would be easier to explain that way - but I can't atm.

    This IS NOT a complaint. I absolutely love the spreadsheet - I just wanted to mention it in case you want it corrected.
    Thank you for all the work you've put into it - my inner perfectionist cheers everytime I look at those handy pie charts etc :D

    I would just delete the entry - but I get a warning that i should not so I figured I better ask.
     
    #38 Nimbus, Mar 29, 2017
    Last edited by a moderator: Mar 29, 2017
    3 people like this.
  19. Sad Panda

    Joined:
    Mar 11, 2017
    Messages:
    391
    Likes Received:
    922
    Very minor error, yes. M21 (T7 Male for Breeding Results by Range/Tier/Gender cell) should =G21 and not G26. I'm sure it will be corrected as soon as he can :) Thanks for the heads up
     
    2 people like this.
  20. Horse Breeder Ralphy

    Joined:
    Mar 15, 2017
    Messages:
    871
    Likes Received:
    883
    Thank you for spotting it! I thought I had combed through every cell double-checking, but apparently I missed one. =/

    As @Sad Panda mentioned, the error is that the formula in M21 should be '=G21' instead of '=G26'. I've already updated the sheet that I've shared. But it's easy enough to change yourself if you wish. Just click on the cell M21 and change it in the formula bar at the top (though you'll likely get a warning message to confirm that the locked cell can be changed).
     
    2 people like this.

Share This Page