There are a bunch of wonderful apps and location-based services for managing supply lines, keeping track of door-to-door sales associates, and optimizing these paths. But I came up with a quick model using Google Spreadsheets.
The basic idea is that you have a map with n locations that you have to visit. The goal is to find out how many places you could go in a day based on a bunch of parameters (distance between points, average speed, etc.). So how do you do it?
The first thing I did was determine what my map would look like. In the real world you would actually know the distances between each location and how many locations you have. In this case I don't, so I guessed using a mathematically-pleasing design. I decided on a orthographic projection of a rectified 5-cell pentagon, which is a complicated way of saying, in 2 dimensions, a small pentagon inside of a big pentagon.
To do the calculations you can determine distances from each points as long as you know the length of each side (T) and the length of diagonals (D) of the pentagons. You can adjust this in I2 and I3 on each of the person tabs. Other variables you can change include average speed while traveling, amount of time spent at each location, some lag time to account for variation, and number of hours worked per day.
Once you have added all of your information you can get a forecasted number of locations to be visited per day as well as other random information. I've also included a sensitivity analysis by varying time at location and lag time, all as percentage change from current forecast.
If you'd like to play around with if you can access the spreadsheet here and go to File>Make a copy for an editable version.
Enjoy!
Showing posts with label Methods. Show all posts
Showing posts with label Methods. Show all posts
Saturday, September 22, 2012
Wednesday, March 14, 2012
Update on March Madness Pool Templates (How I fixed it)
It seems that the =googletournament() function will most likely not be updated in time. For those still wishing to use Google Docs for your own bracket or Pool, however, you still have an option. I've updated the templates I created to still work. Instead of getting your information from Googleland you will be getting it from me. As long as you don't change any grey background fields on the spreadsheets, your brackets will update with the winners automatically, assuming I am near a computer at the completion of a game (I probably will be as I'm a huge basketball fan).
Since this blog is about Google Docs, I thought I might quickly step into that I did on the backend of these templates to make it work.
I decided that my method would be to create a master spreadsheet that I update. My template then uses =importrange() to pull various ranges from the master spreadsheet. As long as you have viewing rights to a spreadsheet, you can pull data from it using importrange(). So this should work.
I divided the Full Bracket tab for both the Single and Group Template into 3 sections; the left side of the bracket, the right side of the bracket and the middle. This way I only had to write 3 importrange() functions. I decided against using one. Now, the Full Bracket tab is pulling in the data from my master spreadsheet in three sections. As soon as I update the master sheet, anybody who references my master sheet (anybody using my templates) will see their data update within a few minutes. The blank bracket templates simply reference the Full Bracket so that we don't end up with a million importrange() function (you can only actually have up to 50).
If anything, the spreadsheets might actually be quicker because 3 importrange() functions should be lighter than a billion =googletournament() functions.
Thanks for being patient. Hopefully next year Google will be able to fix the function so that it works (I'll make sure of it this time).
Cheers and good luck with your brackets!
Since this blog is about Google Docs, I thought I might quickly step into that I did on the backend of these templates to make it work.
I decided that my method would be to create a master spreadsheet that I update. My template then uses =importrange() to pull various ranges from the master spreadsheet. As long as you have viewing rights to a spreadsheet, you can pull data from it using importrange(). So this should work.
I divided the Full Bracket tab for both the Single and Group Template into 3 sections; the left side of the bracket, the right side of the bracket and the middle. This way I only had to write 3 importrange() functions. I decided against using one. Now, the Full Bracket tab is pulling in the data from my master spreadsheet in three sections. As soon as I update the master sheet, anybody who references my master sheet (anybody using my templates) will see their data update within a few minutes. The blank bracket templates simply reference the Full Bracket so that we don't end up with a million importrange() function (you can only actually have up to 50).
If anything, the spreadsheets might actually be quicker because 3 importrange() functions should be lighter than a billion =googletournament() functions.
Thanks for being patient. Hopefully next year Google will be able to fix the function so that it works (I'll make sure of it this time).
Cheers and good luck with your brackets!
Sunday, March 11, 2012
March Madness Bracket/Pool Templates
The long awaited Bracket has arrived! See below for links to the template. You will have to make your own copies. Note: The templates will not work until the field is set and Google adds the information to the =googletournament() function.
Individual Bracket
The individual bracket can be used for you to keep track of your own bracket, or serve as a template to be used in a group bracket pool. The group will be discussed below.
Full Bracket:
The Full Bracket tab shows what the tournament currently looks like. You don't make any selections here but this shows how powerful =googletournament() is. The only editable cells are for choosing men or women and then the year. This bracket was built with the men's tournament in mind and has not been tested for the women's tournament.
Points:
This tab shows you the current points you have won for your picks. You can choose how many points are given each round. I recommend using an exponential system as it gets more difficult to predict games that happen later in the tournament. The exponential system is the default. This tab is also where you apply the tiebreaker. This number will show up after the final results of the final game. The tie breaker asks each person to guess at the final score of the championship game. The actual summed scores for each time in the final game is subtracted from the guesses of the final scores for each time in the final game. This creates a differential between the actual results and the guessed results. In the event of a tie, whoever has the smaller differential wins. The absolute value is taken so there is not a penalty for going over.
Your Bracket:
This is where you make your choices. Only edit the yellow cells and make sure to choose what you think the final score will be. The game cells are validated so you can only choose the possible teams. Make sure you put your name in the name box and email address if you are part of a group. This will help the pool admin with sending you information. Note: You will not be able to fully fill out the bracket until the field is completely set.
Helper:
This sheet is hidden but builds the validation for each team choice. You can click around to see what it does. Basically, it looks at the choices you made in the previous round and only allows you to choose one of the possible two teams. This is an upgrade from the past in that you could type pretty much anything in the cells.
HELP:
The HELP sheet gives a few helper instructions.
Group Bracket
The group bracket creates a place for many brackets to be analyzed for scoring and competition. The current bracket has room for 50 brackets. I'm not sure how many brackets the template can take before becoming slow. However, if you run into this issue, I would advise making "Groups" and aggregating the Standings tab in a separate Standings spreadsheet to keep track of who is winning. I'll comment on doing that below.
Full Bracket:
This tab is the exact same as in the single bracket template.
Bracket Participants:
Use this sheet to indicate who is participating in your pool. You will only need to update the Name column. Make sure to include First and Last name or some sort of identifying to make sure that everyone in the pool has unique usernames. This is important for scoring purposes. The name that you put here must match the name of the tab that corresponds with the person. For example, if Big Bird was in our pool we must type Big Bird on the Bracket Participants tab and the bracket corresponding to Big Bird must say exactly Big Bird's Bracket.
Standings:
Standings sorts all of the pool participants by score and then by differential. As a reminder, the absolute value of the differential is taken so there is no penalty for going over. You don't need to touch anything on this tab.
Points:
If there is a tab that is going to bog things down, it will be this tab. This tab scores the brackets by grabbing all of the important information from each bracket and scoring it against the actual results of the tournament by assigning points for correct guesses. If you would like to change the point values for each round you can do it here in column C. You should only update the cells with a yellow background. This sheet uses the indirect() function to search through the Spreadsheet for a bracket that matches a name from the Bracket Participants tab. This is why the tab name of the individual must match exactly the name in the participants. If this tab starts bogging down the entire spreadsheet, see below for some tips.
HELP:
This tab provides simple help information about the group version of the spreadsheet.
Blank Bracket:
Here is a blank bracket for using one method of setting up the pool. I will now discuss two methods below.
Setting up your pool Method 1
The first method for setting up your pool requires you to make multiple copies of the blank bracket tab. You will need a copy for each participant in your pool. These tabs much be renamed for the participant and match the name exactly in this format "[Name]'s Bracket". I suggest using unique identifiers or first name and last name. Once you have this set up for each person in your pool, you will need to share this spreadsheet with them. You can either share the entire spreadsheet and hope that users don't edit other's brackets or use tab level permissions (click on the down arrow for the tab and go to Protection to choose who can edit the tab) and allow only the user whose name is on the tab to edit the tab. I would suggest this method for those who have a few people in the pool (<10 or so). Once everyone has set up their bracket you can change the editing rights to view only for all collaborators so that they cannot change their results.
Update: You will not be able to use the Helper for validation on the Blank Bracket. If you use this method your participants must type the short name for the team you choose to win. Therefore, as a bracket admin someone must make sure each choice is a legitimate choice. This is a disadvantage to Method 1. Thanks to Hells_ for pointing this out!
Setting up your pool Method 2
If you have a lot of people in your pool you might consider having multiple Group brackets and requiring your pool participants to fill out their own single bracket and share it with you. The steps for achieving this are as follows:
Individual Bracket
The individual bracket can be used for you to keep track of your own bracket, or serve as a template to be used in a group bracket pool. The group will be discussed below.
Full Bracket:
The Full Bracket tab shows what the tournament currently looks like. You don't make any selections here but this shows how powerful =googletournament() is. The only editable cells are for choosing men or women and then the year. This bracket was built with the men's tournament in mind and has not been tested for the women's tournament.
Points:
This tab shows you the current points you have won for your picks. You can choose how many points are given each round. I recommend using an exponential system as it gets more difficult to predict games that happen later in the tournament. The exponential system is the default. This tab is also where you apply the tiebreaker. This number will show up after the final results of the final game. The tie breaker asks each person to guess at the final score of the championship game. The actual summed scores for each time in the final game is subtracted from the guesses of the final scores for each time in the final game. This creates a differential between the actual results and the guessed results. In the event of a tie, whoever has the smaller differential wins. The absolute value is taken so there is not a penalty for going over.
Your Bracket:
This is where you make your choices. Only edit the yellow cells and make sure to choose what you think the final score will be. The game cells are validated so you can only choose the possible teams. Make sure you put your name in the name box and email address if you are part of a group. This will help the pool admin with sending you information. Note: You will not be able to fully fill out the bracket until the field is completely set.
Helper:
This sheet is hidden but builds the validation for each team choice. You can click around to see what it does. Basically, it looks at the choices you made in the previous round and only allows you to choose one of the possible two teams. This is an upgrade from the past in that you could type pretty much anything in the cells.
HELP:
The HELP sheet gives a few helper instructions.
Group Bracket
The group bracket creates a place for many brackets to be analyzed for scoring and competition. The current bracket has room for 50 brackets. I'm not sure how many brackets the template can take before becoming slow. However, if you run into this issue, I would advise making "Groups" and aggregating the Standings tab in a separate Standings spreadsheet to keep track of who is winning. I'll comment on doing that below.
Full Bracket:
This tab is the exact same as in the single bracket template.
Bracket Participants:
Use this sheet to indicate who is participating in your pool. You will only need to update the Name column. Make sure to include First and Last name or some sort of identifying to make sure that everyone in the pool has unique usernames. This is important for scoring purposes. The name that you put here must match the name of the tab that corresponds with the person. For example, if Big Bird was in our pool we must type Big Bird on the Bracket Participants tab and the bracket corresponding to Big Bird must say exactly Big Bird's Bracket.
Standings:
Standings sorts all of the pool participants by score and then by differential. As a reminder, the absolute value of the differential is taken so there is no penalty for going over. You don't need to touch anything on this tab.
Points:
If there is a tab that is going to bog things down, it will be this tab. This tab scores the brackets by grabbing all of the important information from each bracket and scoring it against the actual results of the tournament by assigning points for correct guesses. If you would like to change the point values for each round you can do it here in column C. You should only update the cells with a yellow background. This sheet uses the indirect() function to search through the Spreadsheet for a bracket that matches a name from the Bracket Participants tab. This is why the tab name of the individual must match exactly the name in the participants. If this tab starts bogging down the entire spreadsheet, see below for some tips.
HELP:
This tab provides simple help information about the group version of the spreadsheet.
Blank Bracket:
Here is a blank bracket for using one method of setting up the pool. I will now discuss two methods below.
Setting up your pool Method 1
The first method for setting up your pool requires you to make multiple copies of the blank bracket tab. You will need a copy for each participant in your pool. These tabs much be renamed for the participant and match the name exactly in this format "[Name]'s Bracket". I suggest using unique identifiers or first name and last name. Once you have this set up for each person in your pool, you will need to share this spreadsheet with them. You can either share the entire spreadsheet and hope that users don't edit other's brackets or use tab level permissions (click on the down arrow for the tab and go to Protection to choose who can edit the tab) and allow only the user whose name is on the tab to edit the tab. I would suggest this method for those who have a few people in the pool (<10 or so). Once everyone has set up their bracket you can change the editing rights to view only for all collaborators so that they cannot change their results.
Update: You will not be able to use the Helper for validation on the Blank Bracket. If you use this method your participants must type the short name for the team you choose to win. Therefore, as a bracket admin someone must make sure each choice is a legitimate choice. This is a disadvantage to Method 1. Thanks to Hells_ for pointing this out!
Setting up your pool Method 2
If you have a lot of people in your pool you might consider having multiple Group brackets and requiring your pool participants to fill out their own single bracket and share it with you. The steps for achieving this are as follows:
- Share the Single Bracket as view-only (or link them to the single bracket template above)
- Ask them to make a copy of this sheet and rename accordingly (they will have to be signed into a Google Account)
- Have them fill out the bracket and rename the "Your Bracket" tab with their name
- Once they have completed the bracket, they must share it with whoever is running the group pool
- Once shared, the pool admin must open the shared bracket, select the tab that shows their choices and choose "Copy to..."
- Select the group bracket as the target location of the copy.
- Open the group bracket and you will see a "Copy of [Name]'s bracket"
- Rename the tab as "[Name]'s Bracket" and add their name that matches their bracket name to the list of participants.
- OPTIONAL: Share the Group bracket with this person.
- You're Done! Now watch the tournament!
Tips
- If the spreadsheet becomes bogged down, create multiple group spreadsheets and assign people to a group alphabetically. For scoring, you can then create a separate spreadsheet that uses importrange() to grab the Standings tab from each group. You can use vmerge() from the scripts gallery to join the data or lock the ranges to grab only x amount of rows. Then you can use a filter() or query() on your data to find out who is winning. See a template below for doing that.
- If the spreadsheet becomes bogged down considering copying and pasting values only on the Points tab. Since the Points tab grabs the winning choices from the individual brackets you really only need to do this once. After the brackets are "locked" you can get rid of all of the indirect() functions by copying and pasting values only. Just know that any changes you make to an individual bracket will not be updated on the points. But this shouldn't matter because people shouldn't be making changes to brackets after the tournament has started.
Final Thoughts
This might seem somewhat complicated but I think it really shows the power of having a spreadsheets online, collaboration, custom functions, and integration with other Google products. I'm running a pool where I created a Google Site to hold all of the information using Method 2. I've included a link to an example site that will be created once the tournament gets going. Enjoy!
If you have any questions, feel free to comment below. I also might hold a Hangout on Wednesday night CST to entertain any questions or just talk about how this works. Add me to a circle on Google+ to learn more about that.
...and since I'm from St. Louis...GO BILLS!
Cheers!
Saturday, February 25, 2012
March Madness Is Coming!!!
If you are as much of a basketball fan as I am then you are getting excited for March Madness!! But since this is a Blog devoted to Google Docs, I guess I should connect it someway. Bracketology anybody? I'm currently working on a Google Docs based bracket that will take advantage of the =googletournament() function.
I'm working on two versions. One version will be set so that you and your friends can compete against each other and have multiple brackets in a pool. The other version is a single bracket so you can keep track of your own score!
Currently, features include automatic scoring, validated team choices so you can only choose the available teams for that certain game, customizable options, a standings view and information about each team and game!
The biggest hurdle is filling out the bracket. Due to the current limitations of forms, a form input is just not feasible. Filling out the bracket will require each of your team members to fill out their own bracket, or you can share only their bracket using tab level permissions in spreadsheets.
Don't worry; there will be plenty of instruction on how to use it. But stay tuned!
If you have any recommendations for features that you would like to see on the bracket please put it in the comments!
Cheers!
I'm working on two versions. One version will be set so that you and your friends can compete against each other and have multiple brackets in a pool. The other version is a single bracket so you can keep track of your own score!
Currently, features include automatic scoring, validated team choices so you can only choose the available teams for that certain game, customizable options, a standings view and information about each team and game!
The biggest hurdle is filling out the bracket. Due to the current limitations of forms, a form input is just not feasible. Filling out the bracket will require each of your team members to fill out their own bracket, or you can share only their bracket using tab level permissions in spreadsheets.
Don't worry; there will be plenty of instruction on how to use it. But stay tuned!
If you have any recommendations for features that you would like to see on the bracket please put it in the comments!
Cheers!
Tuesday, October 11, 2011
Using =split() to create column headers: A "dripping awesome" result
Asker Anbotero asked the following question on the Docs forum yesterday:
"I happen to have columns A and B, each filled with “X”s:
A BX
X
X X
X
X X
X X
How do I count the number of cells filled in A that are not in B, and vice-versa? There is this undocumented Google Docs function, but I couldn’t understand how it works: COUNTUNIQUE().
For A it should be 3, and for B just 2."
Adam provides a wonderful and quick answers for finding the answer for column A:
=ArrayFormula( SUM( ( A:A = "X" ) * ( B:B <> "X" ) ) )
This formula must be applied in a column that is not A or B because his ranges use open ends. If this formula was placed in a cell in column A or B the formula would error as a circular dependency. His formula basically counts the number of times there is an X in A and not in B which is exactly what Anbotero was trying to solve. To amend this for column B it would look like:
=ArrayFormula( SUM( ( A:A = "X" ) * ( B:B <> "X" ) ) )
This formula must be applied in a column that is not A or B because his ranges use open ends. If this formula was placed in a cell in column A or B the formula would error as a circular dependency. His formula basically counts the number of times there is an X in A and not in B which is exactly what Anbotero was trying to solve. To amend this for column B it would look like:
=ArrayFormula( SUM( ( B:B = "X" ) * ( A:A <> "X" ) ) ).
So, while Adam was dutifully providing this answer I was trying to figure out how to do this with one single formula. Here is what I came up with:
The formula:
So, while Adam was dutifully providing this answer I was trying to figure out how to do this with one single formula. Here is what I came up with:
Click here to make your own copy to play with.
The formula:
=arrayformula(if(row(A1:B2)=1,split("UniqueCol1xUniqueCol2","x"),split(countif(arrayformula(if(A3:A=B3:B,1,if(A3:A="X",2,3))),"2")&"x"&countif(arrayformula(if(A3:A=B3:B,1,if(A3:A="X",2,3))),"3"),"x")))
I'm more interested in explaining the method as to drilling down the function but if you want to know feel free to write in the comments and I will expand further. The function creates a 2x2 table. The first row basically takes the string UniqueCol1xUniqueCol2 and "splits" it between A1 and B1. The function knows to split when it sees "x", the delimiter, in the string. UniqueCol1xUniqueCol2 becomes UniqueCol1 | UniqueCol2.
The function knows to do this because of the first condition in the if statement. In general, if you want to create a row with headers always use =row() for the first condition in an if statement. Set this equal to the row number where you want the column headers to begin. In this example, I want the column headers to appear in row 1. Therefore, I use =if(row(A1:B2)=1...
The same is done for the second row but I don't need to define this row because I'm only using 2 rows in my range. Therefore, if its row 1 put my headers, if its not then do my calculations. For the calculations, the method of using =split() is the same. I do the calculation for column A, join it with x and then join it with the calculation for column B. Before the split, the function ends up returning 3x4. Then =split() does the same with 3x4 as it did with UniqueCol1xUniqueCol2.
There you have it! Yogi also just posted another method on his blog. All of these different solutions truly show how robust Google Spreadsheets can be (or how addicted the Docs TCs are!).
Cheers!
PS: Adam's query solution also works! See Sheet2 on my spreadsheet for that example.
I'm more interested in explaining the method as to drilling down the function but if you want to know feel free to write in the comments and I will expand further. The function creates a 2x2 table. The first row basically takes the string UniqueCol1xUniqueCol2 and "splits" it between A1 and B1. The function knows to split when it sees "x", the delimiter, in the string. UniqueCol1xUniqueCol2 becomes UniqueCol1 | UniqueCol2.
The function knows to do this because of the first condition in the if statement. In general, if you want to create a row with headers always use =row() for the first condition in an if statement. Set this equal to the row number where you want the column headers to begin. In this example, I want the column headers to appear in row 1. Therefore, I use =if(row(A1:B2)=1...
The same is done for the second row but I don't need to define this row because I'm only using 2 rows in my range. Therefore, if its row 1 put my headers, if its not then do my calculations. For the calculations, the method of using =split() is the same. I do the calculation for column A, join it with x and then join it with the calculation for column B. Before the split, the function ends up returning 3x4. Then =split() does the same with 3x4 as it did with UniqueCol1xUniqueCol2.
There you have it! Yogi also just posted another method on his blog. All of these different solutions truly show how robust Google Spreadsheets can be (or how addicted the Docs TCs are!).
Cheers!
PS: Adam's query solution also works! See Sheet2 on my spreadsheet for that example.
Subscribe to:
Posts (Atom)