Spreadsheet Solving

Build a Math Word Problem Worksheet Generator

Whether you love or hate the Common Core, to be successful at the test one needs to able to solve math word problems. Today’s spreadsheet will allow you to practice by generating a unique set of math word problems each time it is refreshed. Special thanks to my coworker V for the idea (or alternatively, you know who to blame).

Spreadsheet Inputs

The goal for today’s spreadsheet is to generate five word problems with randomized inputs for the numbers. Word problems tend to follow similar patterns, and we’ll try to replicate some common word problems. There are many different types of word problems (this set of 120 of them from Prodigy is a good example), but maybe we’ll stick to some simple addition and subtraction problems for now.

We’ll make our own simple problems where (some person) has (some number) of an object and either gets (some number) more or less, and then add some small variations.

For the person, we’ll randomly pick one of the top 50 girls and boys names from the year 2019. We’ll also randomly pick the object. This will go on a separate tab to keep things clean:

Our main user input will be how large they want the numbers to add or subtract to be, essentially adjusting for the difficulty:

Then we’ll go back to the second page to create some word problem templates. We’ll generate a random number between 1-100 for the name, identify the gender, take a random object between with a random number between 1-26, then generate enough random numbers for each type of problem.

Here we made formulas to generate word problems for 2 number addition and subtraction:

The next steps were a little involved, and required formatting, merging and centering cells. To use the sheet, one would need to paste in the desired templates for each of the 5 questions. One could also create new templates to paste in:

Check out the spreadsheet here: Math Word Problem Generator

If you like today’s post, check out the other customizable kids educational spreadsheets that we’ve created during this pandemic:

How to chart a tennis match with an Excel Spreadsheet

The return of live sports has been a welcome development during the COVID lockdown. We’ve enjoyed watching the US Open and seeing the new batch of young players that are going deep into the tournament.

Today’s post is for those parents who are dreaming of developing their kids into the next Roger Federer, Rafael Nadal, or more realistically (insert name of solid high school player who is able to get recruited into a highly ranked college).

The inspiration for this post comes from http://thetennisparentsbible.com/, a very in-depth (and intimidating) book on junior tennis development. One of the sections of the book is on match charting, or effectively keeping stats for your child’s match. Some of the benefits that author Frank Giampaolo cite are systematically evaluating performance, identifying strengths and weaknesses, and looking busy so you don’t have to talk to somewhat neurotic tennis parents (okay, I added the last one).

Spreadsheet Inputs

This spreadsheet is a bit tricky because of the amount of complexity that one could try for. In theory, every stroke could be a data point, including where on the court it was hit from, what spin it was hit with, and where on the court it was hit to. I’ll keep it relatively simple for now…

Basically we’ll keep track of who won the point, how many shots were in the rally, and how the point was won (winner or error, forehand or backhand, volley or groundstroke or serve). This system was inspired by someone named “Knotwilg” commenting on the TennisTroll Channel.

Here’s how our spreadsheet looks so far:

Here’s how I’d fill it out. If Player 1 hits an ace on the first point, I’d put a “W” into cell B6. Then if in the next point Player 2 dumps a forehand volley into the net, I’d put an “E” into cell J7. There will be lots of borderline situations (half volleys? forced error or winner? is a tweener a forehand or a backhand?), but out of simplicity we’ll keep it to just these five categories and winner or error, and let the user decide how to categorize the borderline cases.

Spreadsheet Outputs

Now that we have some raw data of how all the points in a match were won or lost, we can create some summary statistics to give us some insights into how and why the match was won or lost.

I’ll insert some rows above the match chart to calculate some of these summary statistics. I’m sticking to the basics here – first we’ll count Player 1’s winners:

The other formulas follow similar logic and break it out by strokes. We then add percentages to the winners versus errors.

Finally, we add some data on the rally length, including the overall average and the average rally length for when each player wins to give a sense of whether a player does better drawing out rallies or ending them faster. This includes adding an intermediate step of figuring out which player won the point:

And that’s pretty much it for this simple version of a tennis match tracker. In theory, you could customize for statistics you think are most important (like where on the court each player was to calculate things like net points won, etc). Also, one could build something that tracks matches over time. All great exercises for building your spreadsheet skills!

Download the spreadsheet here: Tennis Match Tracker

Build a Math Pattern Recognition Worksheet

Well, we’ve officially lost count of how many weeks of quarantine have passed. For us, quarantine started with a nice streak of weeks posting educational kids activities spreadsheets. Now our time is mostly filled with work, kids, trying to stay sane, and streaming video services.

Today’s spreadsheet generates a unique math pattern recognition spreadsheet for kids. It refreshes each time the key F9 is pressed. You can adjust the difficulty level of this sheet by setting inputs, such as the size of the numbers and increments, the number of blank spaces to fill in with more blanks adding more challenge, and whether to use addition, subtraction or multiplication.

Feeling way too lazy to walk step by step through how this was built, we include the link of the spreadsheet below. Feel free to download and click through the formulas yourself! (Hint – the formulas are hidden in the four rows after each question, change the cell formatting back to “General” to see it)

Download the spreadsheet: Math Pattern Recognition Worksheet

Teach kids to multiply with this spreadsheet

Today’s spreadsheet may help you introduce the concept of multiplication to your kids. Rather than walk through how to build the sheet step-by-step, we’ll include the download link and focus on how it works and how we tried (only semi-successfully with the bribe of TV) to use it.

Download the spreadsheet here: Multiplication Spreadsheet

How it Works

The spreadsheet includes 2 tables – a visualization grid and a traditional times-table.

First, input any two numbers (from 1 to 20) you wish to multiply. In our example, we entered 5 x 5 by putting 5 in the input cells B4 and B5 (highlighted in yellow). You can go as low as 1 x 1 and up to 20 x 20.

Now let’s explore the visualization grid (on the left) and the traditional times-table.

How to Use it

Visualization Grid: This grid is designed to help kids visualize the multiplication concept, by tapping into their understanding of counting and addition.

For our example, you will see a a 5 x 5 block of 25 pink cells. As a start, the kids can count the cells. Next, you can direct them to the blue row on top across the top of the grid. Here you can see that 5 x 5 is broken out into a more familiar addition problem of 5 + 5 + 5 + 5 +5 = 25.

Times-Table: On the right is a standard times table, where we tried to highlight the relevant square. So, in our example, you’ll see that the inputs 5 and 5 are highlighted in red and the answer, 25 is highlighted in yellow.

You’ll notice that if you try different combinations, you may see more than one cell in yellow. For instance, if we used 4 x 5, then every cell with 20 will be in yellow (this is a conditional formatting nightmare).

Let us know if you tried this sheet out with your kids or if have some elegant solution for formatting the right table!

Build a Scrabble two and three letter word memorization training spreadsheet

While a big vocabulary is an important part of Scrabble success, an arguably more important Scrabble “skill” is memorizing all of the two-letter, and at a more advanced level, three-letter words. A well-played “QI” or “XI” or “QAT” can be the difference between winning and losing. This sheet is the adult version of last week’s Sight Words Quiz for kids.

In today’s post, we’ll build a quiz in which you can type in the first, second, or third letter, and the spreadsheet will return a random two or three letter word that matches the input.

Spreadsheet inputs

We’ll use a complete list of two and three letter words using this website. We’ll also add in a little logic in columns B-D that breaks out each word into its individual three letters, which we’ll need for later:

Note that we’ll start the data on line 15, since we’ll likely have our interface and some calculations in row 1-13. We’ll have separate tabs for our two-letter and three-letter quizzes. We went with Google Sheets so that in theory you could pull this sheet out on your phone during some downtime on the subway…whenever we’re able to ride those again…

Spreadsheet Calculations and Output

You can enter in any of the three letters in the words you want to pull, then put in some logic to assign a 1 to the words that match the user inputs and a 0 if they don’t match.

Here’s my attempt to explain what I did: Columns E,F,G check to see if each of your letters match the current word, and returns a 1 if it matches, 0 if it doesn’t. Column H checks to see if all three letters matched by seeing if E,F,G sum to 3. Column I assigns a unique number to each match, and then Column J cleans up Column I by setting all the non-matches to 0. Columns K and L look up all the matches in order.

It’s probably better understood if you download the spreadsheet and walk through the formulas.

Here’s what it looks like, and I pasted in what the key formulas look like in rows 11 and 12:

Note that this sheet provides a quiz, where one word is randomly drawn, but you can also just scroll through column L to see all the possible Scrabble words that match the search criteria.

Download the spreadsheet here (reminder, you can Make a copy of the spreadsheet and save it to your Google Drive by going to FIle –> Make a Copy:

Google Spreadsheet: Spreadsheet Solving Scrabble Quiz

Here’s a Google Sheets tip: To generate a random number, you have to recalculate the Sheet. In Microsoft Excel, you just have to hit F9 to recalculate. In Google Sheets you can just hit Ctrl+R in any cell, which effectively copies the cell to itself, but also has the benefit of recalculating the sheet and generating a new random quiz word.

Build a Kids Sight Words quiz spreadsheet

Coronavirus lockdown week 11? – Let’s continue with what has now become a series of posts on educational kids activities spreadsheets. As a reminder, Part 1 was a Math Dice spreadsheet game and Part 2 was a Custom Math Worksheet Generator. Today’s spreadsheet will hop over to the English language part of elementary education and help you quiz your kids on their sight words.

Our goal is to take a list of sight words and randomly pull one word every time the sheet is refreshed so you can quiz your child. Sight words are frequently occurring words that kids should memorize rather than sound out.

Spreadsheet Inputs

The list of Dolch sight words. Edward William Dolch compiled this list of words, which were published in a journal in 1936.

The grade level. We’ll do a little trick here where we add a drop down menu by using the Data Validation function to select from the list of possible grade levels in the above screen, cells F4:F48.

Spreadsheet Output

Once you pick a grade level, the spreadsheet will use the Randbetween() function to generate a random number between 1 and the maximum number of words in that grade level. Then it uses a vlook function to return the word with which you can torture test your child.

Try it out yourself by downloading the spreadsheet here: Dolch Sight Words quiz spreadsheet. Remember to hit the F9 key to recalculate the worksheet and generate a new random sight word.

Build a Printable Math Worksheet Generator Spreadsheet

Now two months into lockdown, we’re already scraping the bottom of the barrel when it comes to kids’ activities. No one wants to play our Math Dice Spreadsheet anymore. Why not torture educate them with a potentially never-ending stream of repetitive math reinforcement? Kidding aside, a mastery of arithmetic is a prerequisite for the (only slightly) more advanced problem solving examples that we provide here at spreadsheetsolving.com…

We basically want to create a spreadsheet that creates a bunch of random arithmetic problems that looks like this and can be printed:

Spreadsheet Inputs

What arithmetic operator we are practicing (+,-,x,/)
The largest possible value of the first number (one can adjust the difficulty by allowing more digits for each number you are working with.
The largest possible value of the second number.

Spreadsheet Outputs

This is another relatively simple spreadsheet – we’ll again use the randbetween() function to generate random numbers between 0 and the maximum values. The challenge with this spreadsheet is the formatting and making things look presentable. I’ll save you the gory details and just post the file here:

Download the Excel .xls file by clicking here: Custom Math Worksheet Generator

For another spreadsheet that combines both addition and subtraction on the same sheet, click here: Custom Math Worksheet Generator 2

A simple scoreboard spreadsheet for lockdown Zoom games

Here’s a simple spreadsheet that serves as a handy scoreboard to keep track of who is winning your Zoom games. While there’s not much to it, feel free to save a copy to your Google Docs folder. We’ve been playing some Jackbox Party Pack, which keeps score for you, but I could see how there could be other situations (competitive Zoom rock paper scissors?) where you could have your own scoreboard.

Anyways, basically the only calculations involved are the Sum() and Rank() functions. I added in a little bit of formatting (freezing at Row 9 if the game goes many rounds, and shading every other row).

Stay Safe and enjoy!

Spreadsheet is attached here: Simple Scoreboard

(Remember to go to File –> Save a Copy to download a copy to your own Google Sheets Drive that you can Edit).

Build a “Math Dice” Mental Math Mobile Spreadsheet

I remember in 4th grade, a math teacher would write five numbers on the board and ask us to use addition, subtraction, multiplication and division to try arriving at a target number. Like many good things from my childhood, this game has been commercialized into “Math Dice”, where you pay $9.99 for $1.25 worth of dice.

With the help of the randbetween() function, we can create our own unbranded mental math game, fully customizable with the number of “dice” and how large the “dice” can get. We can make it in Google Sheets so you can have it in your pocket anytime the desire to unleash your mental math skills arises.

Spreadsheet Inputs

1) The number of dice/random numbers to use as raw material for calculations.

2) How big the dice/inputs are allowed to be.

3) How big the target number is allowed to be.

Here’s our spreadsheet so far:

Spreadsheet Calcs and Output

Basically, we’ll have a cell for each one of the “dice” that the user wants. The formula will first check to see if we have too many dice, then if not, we generate a random number between 1 and the “Maximum “Dice” Value” input. We then generate a random number for the target value at the bottom.

Here’s how the sheet looks. Also, 2 + (4 x 3) = 14, boom!

Check out the spreadsheet here: Math Dice Google Spreadsheet

You can save a copy of it to your Google Docs Drive to edit it. Every time you alter the sheet, it will generate new random numbers.

Build a Net Worth Estimator Spreadsheet

We’ve just hit week 4 of coronavirus-related working-from-home. Hope everyone is staying safe and staying home if you are able. One thing I’m starting to miss is workplace banter – conversations about TV shows, sports, and celebrity net worths. This post was also inspired by a blog post from Root of Good detailing their net worth progression between 2004 and 2014. In any case, it seems the fascination/financial voyeurism of guessing people’s net worth is widespread, and we’ve built a spreadsheet for the job.

Spreadsheet Inputs

So, here we have another “essence of modeling” problem. We could make our spreadsheet hopelessly complicated and precise with detailed inputs for tax rates, investment allocation, debt, and spending, etc. I’m making the choice to make the spreadsheet be as simple as possible. Our 6 inputs will be:

The year you started working and your starting income. (Or check glassdoor.com to estimate someone else’s).
Your initial net worth – some people have a head start
Your income now – I’m going to assume that you’ve worked every year and your income has gone up (hopefully) in a straight line fashion to today.
The percentage of your gross salary you saved each year. The Root of Good appears to have saved around 70-80% of their income. It appears the national average is closer to 8%.
How aggressively you invested your savings (%stocks vs %cash). I’ve input the annual returns from stocks using this helpful data from slickcharts

We’ll go with Google Sheets for today’s spreadsheet, and just for fun, let’s try to replicate the Root of Good’s numbers:

Spreadsheet calculations and output

In our spreadsheet, we begin with our inputs for the first year. Each year we take the starting net worth, add the income net of expenses to it. We then look up what stocks returned that year and calculate the investment return from their beginning net worth. We add it all together to get the ending net worth for the year:

For each future year, we’ll increase the year by one (until we get to the current year), increase income in a straight line between their current and final income, and add the relevant investment return:

Then we can paste in the formulas from column C into the right columns:

Our spreadsheet would have predicted Root of Good’s net worth in 2014 was $1.48 million. Their post indicated it was $1.35 million. Pretty close for just the few inputs that we used!

Check out the link to our net worth estimator spreadsheet here to play with it yourself. Remember, you can save a copy to your own Google Drive by going to File –> Make a Copy.

It would be a fun exercise if you (anonymously) posted in the comments what the spreadsheet calculated for you versus your actual net worth. That data would be tailor-made for an xy-scatter plot.

Stay home, flatten the COVID-19 curve, and build spreadsheets?

The COVID-19 outbreak has caused many families (including us) to be stuck at home with their kids. While not having to commute has been great, we’re two days in and quickly running out of interesting activities. Perhaps now is the perfect time to learn how to build spreadsheets to analyze data?

If you’re interested, we’ve organized certain spreadsheet skills into a curriculum of sorts, located here. However, the best way to learn spreadsheets (and the motto of our website) is to use them to solve real world problems and figure out how to get through roadblocks on the way.

So today’s spreadsheet is a simple one. We’ll build a sheet that can track coronavirus cases in the US by following the CDC data. First, we navigate to the CDC website tracking COVID-19 cases and then copy in the data near the bottom of cases reported in the US by onset date:

We paste this into our spreadsheet and add a formula in the row below to keep a running tally of cases:

A few clicks later, and we produce this spiffy graph:

The total numbers looked low, so I should probably paste in the data asterisks:

To access this spreadsheet on Google Docs, you can find it here: COVID-19 Case Tracker

Build a Super Bowl squares pool generator spreadsheet

Ah, the Super Bowl – the action, the excitement, the commercials? The only issue with the Super Bowl is for the 97% of Americans located outside of the two metropolitan areas surrounding the two teams playing, the outcome of the game doesn’t really matter as much. To fix that, we have gambling…specifically, the classic Super Bowl squares pool.

For those who are unaware, a Super Bowl squares pool is a 10×10 grid, where each person can purchase a space on the grid. The numbers 0-9 are randomly assigned for each axis with one of the two teams assigned to each axis. Prize payouts usually happen each quarter for the square that matches the ones digit of the actual in game score for each team.

This will be a simple spreadsheet for building a square pool on a Google Spreadsheet:

First, we highlight a 10 x 10 grid, and assign the teams to each axis:

Next, we can build our random 0-9 number generator, first we put the formula “=rand()” in each of the 10 spots, then we use the formula RANK() to rank the random numbers. We have to subtract 1 from the RANK() function because we want the numbers 0-9 while RANK() would produce numbers 1-10.

Do the same thing for the columns, set the stakes and the payout rules, let people pick what squares they want, and then have a “drawing.” The rand() function recalculates after every spreadsheet action, so you’ll want to have an official “next action is final” with everyone around, then immediately screenshot and print the whole table before it recalculates again. Or else people will be suspicious when you have all the 0’s and 7’s…

Here’s the link to the Google Spreadsheet: Super Bowl Squares Pool Generator. Remember, you can access it by going to File->Make a Copy