One of the very first spreadsheets I made on Google docs was my equipment use record. This spreadsheet keeps track of all equipment use on the golf course and allows me to filter the data so I can intensely scrutinize every detail.
The basis for this spreadsheet and pretty much all of my other spreadsheets is the form. The benefit of using a form is that it allows you to keep the data consistent and easy to input into the spreadsheet. Consistent data is extremely important when you decide to filter and sort it out later on. Even the slightest difference such as an extra space can keep the record from working.
The first step is to create a new spreadsheet in Google drive or docs and name it accordingly. I call my sheet "PHGC Equipment Records". I use this naming scheme for all my spreadsheets to keep things consistent.
Once you have done that the next step is to create a form. Goto: Tools--> Create a Form
Creating a form takes a little forethought and careful planning. This is the tool that is used to collect all the data and it needs to be thorough, well thought out and clear and easy to use.
My form starts with a drop down list for who is submitting the record. Drop down lists allow you to keep submissions the same and easy to input. To add a drop down list first select the "Add item" button on the top left of the edit form panel. Then select the "Choose from a list". You can then add all the names of those who will be operating equipment. I start with those who operate the most frequently to make it easier on them.
The next drop down list is which machine the record is for. I don't have very much equipment so I use the model names for this. A larger operation would probably find it easier to assign a number to each piece of equipment to keep is easier to work with.
Next is the hour meter input field. This is where the operator inputs the hour meter reading after they are finished their task on a piece of equipment. This is very important information when scheduling maintenance procedures like oil changes and setups and reel grinding.
Next is another drop down list for different areas of the course. I have it broken down into Greens, Tees, Fairways, Approaches, Rough, Traps and Cart Paths. You could have this broken down into all the areas that you have your course organized in. Some courses mow their green and tee surrounds with a different mower than their rough so I would add Green Surrounds to the list in this instance.
Next up in the form is a list of check boxes to indicate which holes the work was performed. This is especially important if you plan on doing efficiency studies with this data as all parts of the course are different sizes and take different amounts of time to complete. The first selection in my check boxes is "All". This makes it easy for the person filling out the form. I have made this question mandatory as it is possible to not select a box unless this is required.
I also have another drop down box for circle cut directions. I like to alternate directions of circle cuts to avoid laying down the turf. This information shows up on my website and shows the operator the last direction the circle cut was performed in.
You can add many other things to the form such as fuel quantity, amount of grass clippings collected etc. But for now this is basically all that is required.
Once you have your form more or less complete you can close the window and return to your spreadsheet. Remember that you can always go back to the edit form option to add any additional data. Now that you are back in the spreadsheet you will notice that the top row of the first sheet has the headers that you named in your form. When you submit an entry into the form it will appear here. It also includes a nifty time stamp which indicates the exact time the entry was submitted! This is handy as it automatically keeps the date for you which is a pain to manually enter. I always name the sheet where the form data is submitted "formdata" to keep it simple. I don't like spaces or capitals in sheet names as it makes referencing them in the future a bit harder. The formdata sheet should be left alone and you should never manually add any data to this sheet. This is the data in it's raw form and from where you will pull specific filtered data from. If you make an error in submission of the data in the form this is where you will edit it. Remember, only edit existing entries otherwise it can cause all sorts of problems!
Now comes the complicated part, filtering the data to make sense of it all! This is the part that really helps if you are a spreadsheet wiz but I will try and explain it as best as I can. I will provide links to official Google help articles when required.
The easiest way to filter the data is to create a sheet for each individual piece of equipment. I have created one sheet for all equipment but the filtering formula is rather complex. Here I'll show it to you so you can believe me!
=iferror(if(and(B1="All",B2="All",F1="All"),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2),if(and(B1="All",not(B2="All"),not(F1="All")),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2,Formdata!B2:B=B2,Formdata!E2:E=F1),if(and(B1="All",B2="All",not(F1="All")),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2,Formdata!E2:E=F1),if(and(B1="All",not(B2="All"),F1="All"),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2,Formdata!B2:B=B2),if(and(not(B1="All"),not(B2="All"),not(F1="All")),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2,Formdata!C2:C=B1,Formdata!B2:B=B2,Formdata!E2:E=F1),if(and(not(B1="All"),not(B2="All"),F1="All"),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2,Formdata!C2:C=B1,Formdata!B2:B=B2),if(and(not(B1="All"),B2="All",F1="All"),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2,Formdata!C2:C=B1),if(and(not(B1="All"),B2="All",not(F1="All")),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2,Formdata!C2:C=B1,Formdata!E2:E=F1))))))))))
The reason this is so complicated is because I have created drop down lists that include the option "all". This significantly adds to the work required to filter the data!
Yep, that is all required to filter all the data according to operator, date, machine, and area of the course! With this formula I can sort a specific operator on a specific machine mowing a specific area of the course during a specific time frame. It is really cool to compare different operators to see who is getting the best efficiencies and who needs some pointers for improvement.
The formula required for one specific machine is much simpler: =filter(Formdata!A2:I,Formdata!C2:C=$A$2,Formdata!A2:A>$B$1,Formdata!A2:A<=$D$1)
So what you need to do is create a new sheet and name it according to the specific machine you want to see the data from.
I then put the name of the machine up top somewhere as well as the start date you want to filter and the end date. It is important to keep the name of the machine in this cell exactly as it appears in the "formdata" sheet. Often I will submit an entry with that machine and copy/paste the name into this cell to keep it identical. In the cell for the end date I put =today()+1 which keeps the date in this cell current plus one day so that you can see the current day's entries.
I then add the headings found in the "formdata" sheet just below the dates as can be seen in the above image.
To filter out the data for the specific machine I use the filter function. Enter this function directly below the date heading. Your source array should be formdata!A2:A. The "!" tells the spreadsheet that the formdata is a sheet name and not a formula name. This will set the source for the entire A column in your formdata sheet and will automatically expand as you add entries to the form. If you put formdata!A2:A99 it will stop filtering after 99 entries which is bad. When filtering from a formdata sheet always keep the second part of the array open without a row number.
Your first array condition should be to look for and filter only data for the specific machine you want. This will look like formdata!C2:C=$A$1. The C2:C should be whatever column in the "formdata" sheet that contains the names of the equipment and the $A$1 should be the cell in the filtered sheet which contains the name of the machine you want to find and filter. The "quot; is to keep this cell reference the same if you copy the formula to another cell which we will be doing later.
The next array condition should make sure that the entries are for only after the selected date above. this will look like formdata!A2:A>$B$1. This will search the column A in the "formdata" sheet for any dates that are higher (>) than the start date in cell "B1" on your filtered data sheet. B1 should equal whatever cell you chose to be the start date entry.
The next array condition is the end date which looks like formdata!A2:A<=$D$1. The "<=" means less than or equal to an the D1 references the cell where you current date is entered.
That's it for your first column in your filtered sheet. If you used your $ signs properly you should be able to copy this formula to the right and the sheet will filter for each respective column.
Now that you have your first sheet made go to "form-->go to live form" and your form will appear. Enter a submission for you latest filtered equipment sheet and one for another equipment sheet. Now go back you your newly created sheet and you should only see the data for the equipment that you want to see. Cool!
Now if you want to create another sheet for another piece of equipment you can simply select your sheet at the bottom of the page and then select "Duplicate." This will create another identical sheet to the one you just created. Rename this sheet to the piece of equipment you want it to be for and change the name of the equipment in the cell on the top left of the sheet (A1). Just like that and this sheet will be filtering the data for only that piece of equipment!.
You can do this for every piece of equipment you have in your fleet!
Now that you have the basic filtering down you can further edit each page to get more information. I like to add up the total hours that the machine is used but unlike most spreadsheets you have to do this at the top of the sheet rather than the bottom. This is because this sheet will continue to get bigger and bigger automatically as entries are added. I use the "min" and "max" functions for this task. In the cell above the hours input column enter the following =max(D4:D)-min(D4:D). What this does is search the column D for the maximum number then subtracts the minimum number from that column to give you the total hours of use for the specified time frame. The D4:D in this example should reflect whichever column you are trying to find the total hours from.
To make this form more accessible to my staff I have created a staff website that is only accessible to my staff Google account on Google Sites. I have then created a page on the webpage for each form. To add a form to your website in you spreadsheet select "form--->embed form in a webpage..." This will give you some HTML code. Don't be alarmed. Just right click on the code and select copy. Now when you are editing your website go into HTML edit mode and paste the code into the site. This will insert the form into your website and allow your crew to access the form easily on any device with internet access. I have all my forms loaded into my iphone through my website. It doesn't require any fancy apps or programs. Just your simple web browser. What I really like about Google drive, sites and everything else is it easily allows you to adjust who has access to what through their "sharing" options. Almost everything can have its access rights changed.
Congratulations! You now have the starting point for a very powerful data collection tool. I have taken this way further than I have just described but with this collected and sorted data you can now manipulate it any way you want! This is a bit more work to set up than the old fashioned paper forms but it will save you tons of time in the end!
If anyone has any questions about this process please feel free to leave a comment! I would appreciate feedback about things that I could explain more clearly in future posts on the subject.
The basis for this spreadsheet and pretty much all of my other spreadsheets is the form. The benefit of using a form is that it allows you to keep the data consistent and easy to input into the spreadsheet. Consistent data is extremely important when you decide to filter and sort it out later on. Even the slightest difference such as an extra space can keep the record from working.
The first step is to create a new spreadsheet in Google drive or docs and name it accordingly. I call my sheet "PHGC Equipment Records". I use this naming scheme for all my spreadsheets to keep things consistent.
Once you have done that the next step is to create a form. Goto: Tools--> Create a Form
My form starts with a drop down list for who is submitting the record. Drop down lists allow you to keep submissions the same and easy to input. To add a drop down list first select the "Add item" button on the top left of the edit form panel. Then select the "Choose from a list". You can then add all the names of those who will be operating equipment. I start with those who operate the most frequently to make it easier on them.
The next drop down list is which machine the record is for. I don't have very much equipment so I use the model names for this. A larger operation would probably find it easier to assign a number to each piece of equipment to keep is easier to work with.
Next is the hour meter input field. This is where the operator inputs the hour meter reading after they are finished their task on a piece of equipment. This is very important information when scheduling maintenance procedures like oil changes and setups and reel grinding.
Next is another drop down list for different areas of the course. I have it broken down into Greens, Tees, Fairways, Approaches, Rough, Traps and Cart Paths. You could have this broken down into all the areas that you have your course organized in. Some courses mow their green and tee surrounds with a different mower than their rough so I would add Green Surrounds to the list in this instance.
Next up in the form is a list of check boxes to indicate which holes the work was performed. This is especially important if you plan on doing efficiency studies with this data as all parts of the course are different sizes and take different amounts of time to complete. The first selection in my check boxes is "All". This makes it easy for the person filling out the form. I have made this question mandatory as it is possible to not select a box unless this is required.
I also have another drop down box for circle cut directions. I like to alternate directions of circle cuts to avoid laying down the turf. This information shows up on my website and shows the operator the last direction the circle cut was performed in.
You can add many other things to the form such as fuel quantity, amount of grass clippings collected etc. But for now this is basically all that is required.
 |
| formdata sheet with a bunch of data entries |
Now comes the complicated part, filtering the data to make sense of it all! This is the part that really helps if you are a spreadsheet wiz but I will try and explain it as best as I can. I will provide links to official Google help articles when required.
The easiest way to filter the data is to create a sheet for each individual piece of equipment. I have created one sheet for all equipment but the filtering formula is rather complex. Here I'll show it to you so you can believe me!
=iferror(if(and(B1="All",B2="All",F1="All"),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2),if(and(B1="All",not(B2="All"),not(F1="All")),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2,Formdata!B2:B=B2,Formdata!E2:E=F1),if(and(B1="All",B2="All",not(F1="All")),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2,Formdata!E2:E=F1),if(and(B1="All",not(B2="All"),F1="All"),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2,Formdata!B2:B=B2),if(and(not(B1="All"),not(B2="All"),not(F1="All")),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2,Formdata!C2:C=B1,Formdata!B2:B=B2,Formdata!E2:E=F1),if(and(not(B1="All"),not(B2="All"),F1="All"),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2,Formdata!C2:C=B1,Formdata!B2:B=B2),if(and(not(B1="All"),B2="All",F1="All"),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2,Formdata!C2:C=B1),if(and(not(B1="All"),B2="All",not(F1="All")),filter(Formdata!A2:I,Formdata!A2:A>D1,Formdata!A2:A<D2,Formdata!C2:C=B1,Formdata!E2:E=F1))))))))))
The reason this is so complicated is because I have created drop down lists that include the option "all". This significantly adds to the work required to filter the data!
Yep, that is all required to filter all the data according to operator, date, machine, and area of the course! With this formula I can sort a specific operator on a specific machine mowing a specific area of the course during a specific time frame. It is really cool to compare different operators to see who is getting the best efficiencies and who needs some pointers for improvement.
The formula required for one specific machine is much simpler: =filter(Formdata!A2:I,Formdata!C2:C=$A$2,Formdata!A2:A>$B$1,Formdata!A2:A<=$D$1)
So what you need to do is create a new sheet and name it according to the specific machine you want to see the data from.
 |
| Filtered sheet for my 3100 greens mower. |
I then put the name of the machine up top somewhere as well as the start date you want to filter and the end date. It is important to keep the name of the machine in this cell exactly as it appears in the "formdata" sheet. Often I will submit an entry with that machine and copy/paste the name into this cell to keep it identical. In the cell for the end date I put =today()+1 which keeps the date in this cell current plus one day so that you can see the current day's entries.
I then add the headings found in the "formdata" sheet just below the dates as can be seen in the above image.
To filter out the data for the specific machine I use the filter function. Enter this function directly below the date heading. Your source array should be formdata!A2:A. The "!" tells the spreadsheet that the formdata is a sheet name and not a formula name. This will set the source for the entire A column in your formdata sheet and will automatically expand as you add entries to the form. If you put formdata!A2:A99 it will stop filtering after 99 entries which is bad. When filtering from a formdata sheet always keep the second part of the array open without a row number.
Your first array condition should be to look for and filter only data for the specific machine you want. This will look like formdata!C2:C=$A$1. The C2:C should be whatever column in the "formdata" sheet that contains the names of the equipment and the $A$1 should be the cell in the filtered sheet which contains the name of the machine you want to find and filter. The "quot; is to keep this cell reference the same if you copy the formula to another cell which we will be doing later.
The next array condition should make sure that the entries are for only after the selected date above. this will look like formdata!A2:A>$B$1. This will search the column A in the "formdata" sheet for any dates that are higher (>) than the start date in cell "B1" on your filtered data sheet. B1 should equal whatever cell you chose to be the start date entry.
The next array condition is the end date which looks like formdata!A2:A<=$D$1. The "<=" means less than or equal to an the D1 references the cell where you current date is entered.
That's it for your first column in your filtered sheet. If you used your $ signs properly you should be able to copy this formula to the right and the sheet will filter for each respective column.
Now that you have your first sheet made go to "form-->go to live form" and your form will appear. Enter a submission for you latest filtered equipment sheet and one for another equipment sheet. Now go back you your newly created sheet and you should only see the data for the equipment that you want to see. Cool!
Now if you want to create another sheet for another piece of equipment you can simply select your sheet at the bottom of the page and then select "Duplicate." This will create another identical sheet to the one you just created. Rename this sheet to the piece of equipment you want it to be for and change the name of the equipment in the cell on the top left of the sheet (A1). Just like that and this sheet will be filtering the data for only that piece of equipment!.
You can do this for every piece of equipment you have in your fleet!
Now that you have the basic filtering down you can further edit each page to get more information. I like to add up the total hours that the machine is used but unlike most spreadsheets you have to do this at the top of the sheet rather than the bottom. This is because this sheet will continue to get bigger and bigger automatically as entries are added. I use the "min" and "max" functions for this task. In the cell above the hours input column enter the following =max(D4:D)-min(D4:D). What this does is search the column D for the maximum number then subtracts the minimum number from that column to give you the total hours of use for the specified time frame. The D4:D in this example should reflect whichever column you are trying to find the total hours from.
To make this form more accessible to my staff I have created a staff website that is only accessible to my staff Google account on Google Sites. I have then created a page on the webpage for each form. To add a form to your website in you spreadsheet select "form--->embed form in a webpage..." This will give you some HTML code. Don't be alarmed. Just right click on the code and select copy. Now when you are editing your website go into HTML edit mode and paste the code into the site. This will insert the form into your website and allow your crew to access the form easily on any device with internet access. I have all my forms loaded into my iphone through my website. It doesn't require any fancy apps or programs. Just your simple web browser. What I really like about Google drive, sites and everything else is it easily allows you to adjust who has access to what through their "sharing" options. Almost everything can have its access rights changed.
Congratulations! You now have the starting point for a very powerful data collection tool. I have taken this way further than I have just described but with this collected and sorted data you can now manipulate it any way you want! This is a bit more work to set up than the old fashioned paper forms but it will save you tons of time in the end!
If anyone has any questions about this process please feel free to leave a comment! I would appreciate feedback about things that I could explain more clearly in future posts on the subject.