Monday, December 5, 2016

2016 College Football Bowl Prediction Pool Manager with CFB Playoff Bracket

The conference championships were played yesterday which means the 2016 NCAA college football bowl season is here again! Therefore, it’s time to make your picks and predictions about who you think will win each bowl game. One of the best times of the holiday season (other than giving and receiving gifts) is gathering around the TV and rooting for your Alma mater or hometown football team. This year has the added bonus of not just single bowl games but the third year of a four team playoff to determine the national champion.

2016 college football bowl prediction pool


Features for this year's bowl prediction pool over the previous college football bowl pool manager spreadsheets include the following:

  • Easy method to make each bowl game worth a different point value, so the national championship game and semi-finals can be worth more points, or however you want to customize it.
  • Updated leaderboard tab with new stats
  • Separate entry sheet to pass out to participants or co-workers that can be imported by a macro
  • Complete NCAA college football bowl schedule with game times and TV stations
  • New stat sheet to track each conference's record during bowl season (Go Big Ten!)
  • The bowl prediction sheets include the football helmet designs for every team (taken from the 2016 college football helmet schedule spreadsheet), their win-loss record, and the logo for all bowl games. I added the helmets so those players who aren't big college football fans can pick a winner based on their favorite helmet design!

There are now three ways to add participant’s data:

  1. Manual entry using the drop down lists
  2. Copy and paste from the selection sheet to the bowl manager
  3. Use the import macro to automatically import a player’s data into the pool manager by way of a macro 
The beauty of this football bowl manager is you will not have to change or modify any formulas yourself (unless you want to of course). Instructions are included within the Excel file and shows you exactly how to add more players (either manually or by using the button that is linked to a macro). If you enjoy using this sheet football pool manager I would really appreciate it if you would share it with friends, family, and coworkers.

Tweet: I'm using @NTWProductions pool manager in #Excel to predict #cfplayoff & #bowlgames. Get yours here: http://ctt.ec/8EBeO+


Here are some of my Bowl Game Observations that may interest only me...

This year, four teams have made bowl games with losing records:

  1. Mississippi State (5-7)
  2. North Texas (5-7)
  3. Hawaii (6-7)
  4. Central Michigan (6-6 but should be 5-7 because of the fluke win at Oklahoma State)

This could get ugly: Motel 6 Cactus Bowl you have Baylor (6-6) vs. Boise State (10-2) or Birmingham Bowl South Florida (10-2) vs. South Carolina (6-6)

Strangely Intriguing: Popeyes Bahamas Bowl featuring Eastern Michigan (7-5) vs. Old Dominion (9-3). This is EMU’s first bowl berth since 1987 and Old Dominion, playing just their third year at the FBS level, going to its first-ever bowl.

If the MAC’s Western Michigan beats Wisconsin, it will have more Big Ten wins than four teams had in the conference in 2016.


Please let me know if you have any questions, comments, find any bugs, or have any suggestions for improvement. I love that people are using this Bowl Prediction Game to help raise money for charity, that's so awesome to hear! What team are you rooting for?

Thursday, November 24, 2016

2016 Holiday Gift Guide for Excel Users

The 2016 holiday season is officially upon us here in the United States which means it’s time for my annual gift giving guide. I used to panic every year whenever my spouse, parents, and siblings asked me what I wanted for Christmas. I needed to give them an idea otherwise I’d end up with an ugly sweater or some random gadget I would never use. So to help alleviate some of my stress I started compiling my own holiday gift guide. It’s kind of like the big toy catalog you used to get as a kid, only this is for adults. I’ve made a list of items I think would be very useful or exciting for your fellow Excel users, sorted by different categories. Some of these items I already use on a daily basis and others are things that are on my own personal wish list. Enjoy!

MY GO TO EXCEL BOOKS


For just the second time ever, I am giving a discount on my paperback book, 76 Excel Tips to Improve Your Productivity and Efficiency. For 20% off, use discount code: 2F5RYRQC when purchasing directly from CreateSpace here: https://www.createspace.com/4754053. Or you can purchase from Amazon.com here.


Excel: QuickStart Guide - From Beginner to Expert by William Fischer. This Excel book covers everything you need to know about charts, pivot tables, data modeling as well as dashboard design. It is ripe with examples and real-world applications. For those starting in any kind of role in analysis, whether it be as a data scientist, financial analyst, research analyst, or just any type of analyst or manager in general, I would definitely recommend this book.



Excel 2013 Power Programming with VBA by John Walkenbach. This book covers all the methods and tools you need to know in order to program with Excel. Provides tips, tricks, and techniques for expanding Excel's capabilities with VBA that you wont find anywhere else

OTHER BOOKS WORTH READING


The Martian by Andy Weir. If you only read one (fiction) book this year, The Martian has to be the one. I absolutely love this book (and it’s even better than the movie). As soon as I finished it the first time, I immediately re-read it – something I’ve never done before. It’s about an astronaut (with a great sense of humor) who gets left behind on a mission to Mars and has to figure out how to survive. If you’re interested in space exploration, problem solving, engineering, chemistry, botany, or disco + 70s TV shows, I highly recommend you read The Martian. Maybe the best book I’ve read in the past five years.




Catalyst: A Rogue One Story by James Luceno. If you’re a Star Wars fan like I am you’re probably anxiously awaiting Rogue One: A Star Wars Story movie on December 16th. This book will wet your appetite before the new movie comes out. The story takes place during the Clone Wars and the initial formation of the Empire. Not required reading for the movie, but will give you some additional background information on the characters.



What If?: Serious Scientific Answers to Absurd Hypothetical Questions. This book contains hilarious and informative answers to important questions you probably never thought to ask, like: What if everyone on earth aimed a laser pointer at the moon at the same time? What if you could drain all the water from the oceans? What if all the lightning in the world struck the same place? Very fun and interesting read!




Will It Fly? How to Test Your Next Business Idea So You Don’t Waste Your Time and Money by Pat Flynn. I’ve been following Pat’s blog and podcast for a number of years, and you might have seen some of his tips at work on my site. If you’re new to the online business world, this book is fantastic. Lots of practical steps to take to prove whether your idea has validity or not.


Another quick, shameless self-plug. Where are the most terrifying roller coasters found? Who designs them? Which park builds the craziest rides? Find out by reading my book The 50 Most Terrifying Roller Coasters Ever Built! Another reason for including this book on this list is to show you a real life usage of Excel. How's that? Because this is one of the books I wrote using an Excel spreadsheet!




TOOLS FOR THE JOB


Laptop Privacy Screen Protector. Whenever I visit a customer I always take my privacy screen protector for my Dell laptop. It keeps your personal or confidential information safe from prying eyes as you’ll see the information on your display while people on either side only see a darkened screen. If you’re ever on an airplane or in a coffee shop and feel like your neighbor is constantly looking over your shoulder at your screen then you need to get one of these today!



Laser Pointer. This laser pointer always comes in handy when it’s time for a meeting or presentation. Plus, it doubles as a toy to keep your cats entertained.



Keyboard Case for Tablets. How do I get so much done, especially when I’m traveling on the road a lot? I use a combination of a Samsung Galaxy Tablet and my new Keyboard case. They’re small so I can take it almost anywhere and the keyboard allows me to do things like type out this blog post, reply to your email questions, and write Excel macro code.



Excel Quick Reference Sheet - Laminated quick reference showing step-by-step instructions and shortcuts for how to use Microsoft Office Excel 2013.

Microsoft Surface 2: There are newer, more powerful Surface tablets available but they're pretty expensive, running around $1000. The Surface 2 is now pretty affordable at around $250 and is still one of the best tablets for Excel and other Microsoft Office products. In fact, it comes with fully functional versions of Excel, Word, Outlook, Powerpoint, and Onenote for free. As far as I know, no other tablet has anything comparable. 32 and 64GB versions are available.

TECH, GADGETS, AND TOYS


Google Cardboard – Google Cardboard is described as Virtual Reality (VR) on a budget. Gives you a taste of the capabilities of the Oculus Rift at a fraction of the cost. Though be warned it may cause motion sickness if the app you’re looking at doesn’t perfectly track your head movement. It’s fun to assemble and play with. And cheap too.



Dash and Dot – programmable robots. If you’re into programming things like Excel  macros and you want to teach your kids the joys of programming then I’ve found the perfect gift for you. These cute robots are designed to help introduce children to the wonderful world of programming. Oh, and they’re fun for adults too! Can’t wait to use this with my son in a few years. For now he just likes watching me drive it around with my phone.



Solar Powered Christmas Lights. I love putting up Christmas lights and trying to out-do my neighbors, but I have to admit I feel a little guilty about using the additional electricity. Luckily I found these solar powered Christmas lights and they actually work very well! They’re environmentally friendly and they automatically turn on and off each night. They also don’t have to be attached to a power source so I can put them in areas I normally couldn’t string lights.



Fitbit Alta. Let’s face it – us engineers nowadays sit in front of a computer a lot. I didn’t realize how much I wasn’t moving until I started wearing a Fitbit. It now helps me to stay motivated by tracking all-day activity like steps, distance, calories burned and active minutes so I can stay healthy for my family. When I’m sick I can’t answer your emails and help you out, so I use Alta to help stay in shape and on top of my game!




Amazon Prime Membership. If you haven’t joined Amazon Prime yet, why not? I do almost all my shopping online and I get free two-day shipping on nearly everything. You can also borrow books, watch movies, and stream music. Click here to start your 30-day free Amazon Prime trial membership.



YOUR SUGGESTIONS?


Are you putting any of these items on your holiday wish list? If so, let me know which ones in the comments below. Do have anything you’d like to recommend to me?

Monday, August 15, 2016

Weighted Olympic Medal Count 2016

In honor of the 2016 Summer Olympic Games currently being held in Rio, Brazil, I decided to create a Microsoft Excel spreadsheet template for the medal count as I did for the 2014 Winter Olympics and 2012 Summer Olympics. There are two primary methods most websites appear to be ranking the 2016 medal count. Most sites rank countries by the total number of Olympic medals won. Other sites, like the International Olympic Committee (or IOC) rank countries by their gold medal count. And others rank by other factors like per capita or GDP.

If you rank by gold medals countries like Great Britain and China look really good. On the other hand, Japan has 26 medals, ranking fifth overall, but only 27% are gold medals. Therefore, I’ve devised my own ranking system to give each Olympic medal a weight where the silver is worth half a gold medal and a bronze is worth only a quarter of the gold. Based on this new scoring system, the Olympic results suddenly become quite interesting.


Weighted Olympic Medal Count 2016


Pictured below is a bar chart showing all medals won for the top 30 countries plus Michael Phelps (as of the time of this posting on 8-15-16). The bar chart is created in Excel by highlighting the data then going to Insert>Bar>Stacked Bar chart. Change the colors of the bars by right clicking on them then use the drop down menu to select the data you want to change.

2016 summer Olympic medal count chart

Looking at the new Olympic medal ranking systems yields some interesting results. The top four countries actually remain in the same order but Japan drops from fifth to seventh. Spain jumps up seven spots while Colombia jumps up five places. The biggest drop is by Czech Republic, falling 11 places because 5 of their 6 medals are bronze. Download the spreadsheet and see for yourself.




Oh, and if he were a country he’d rank 21st in total medals and in my weighted Olympic medal count he jumps as high as 12th overall because Michael Phelps' medal count at these Olympics Games is five gold and one silver.



I’ve shared my Olympic Medal Count spreadsheet and listed out the Olympic medals by country (as of the morning of August 15th - I will try to keep this updated but no promises!). How would you weight each medal against the others? Comment below and share any of your Olympic medal rating systems!

Wednesday, July 20, 2016

2016 College Football Helmet Schedule Spreadsheet

The 2016 college football season is a little over a month away! I still can’t believe my Buckeyes blew it at home against Michigan State and cost themselves a shot at repeating as Big Ten and National Champions. I have no idea what to expect this season. Looking at the schedule in the Excel file, I’m really worried about that away game against Oklahoma early in the season. At least if they lose, they could have time to recover to get back in the playoff hunt.


It’s fun looking at the 2016 college football helmet schedule and try to predict which games you think your favorite team will win or lose. This spreadsheet of the college football schedules includes every team from all conferences plus independents. Every game is listed as either home, away, or neutral site (noted at the bottom of each sheet).  A college football helmet schedule spreadsheet may be available on other websites but, to my knowledge, this is the only downloadable Excel version and, unlike some of the others, is 100% FREE!


2016 College Football Helmet Schedule Spreadsheet


2016 College Football Playoff Prediction Game

A new feature this year is the edition of the 2016 College Football Playoff Predictor! Week by week, you can pick the four teams who you think will make the playoff at the end of the year. You can see how your prediction changes throughout the year and if it is correct at the end. Crazy that out of 127 teams, only four make the playoff, a measly 3.1 %.  Compare that to the NFL where 12 of 32 makes the playoffs, or 37.5%.


2016 College Football Playoff Prediction Game



Download the football schedule today using the link below:




Suggestions to Improve the College Football Schedule Spreadsheet

I was thinking about other options I could add to make this template more fun, like how much fun the Bowl Prediction Pool is every year. One idea was predicting the top 25 teams compared to the AP Poll every week. What do you think? Do you play any office or fantasy games in regards to college football?

This spreadsheet does take some time to update due to the massive amount of helmets that need to be moved around. In the future I think I plan to make the spreadsheet easier to update and more maintainable. At least maybe figure out how to make the football helmets automatically populate, possibly using drop down lists. Maybe there is also a way to import the schedules from ESPN's website. Any ideas how to make the yearly update go quicker?

Tags: 2016 NCAA Excel Helmet Schedule

Wednesday, July 13, 2016

How to Write a Book Fast by Using Excel - Video Tutorial

Where most authors are writing their books in Microsoft Word, Scrivener, or Pages for Mac, I’ve actually been using Microsoft Excel to write books fast and today I’m going to show you my exact technique. You can see an example of one of my books created in Excel by checking out The 50 Most Terrifying Roller Coasters Ever Built on Amazon.com. As you can see, it has a 4.5 star rating from 14 reviews - none of which are from people I know, all real readers and customer - and has sold more than 2,000 copies. I’ve written four books using this technique and I chose this niche because I have a real passion for roller coasters and amusement parks.

Why write a book in Excel?


List type blog posts and articles are very popular these days, so my idea was to turn one of these type of list posts into a short book. This method is probably not a good idea for every type of book. You wouldn’t want to write a novel or long work of fiction in Excel. But if you're trying to relay stats or facts or have some form of repetition then this technique could help save you a lot of time. Anytime you find yourself doing something over and over or thinking to yourself "there has to be a better way" then, guess what, there probably is a better way and macros could be the answer.

How do you write a book in Excel?

Each row in my spreadsheet template starts a different page in the book. So 50 coasters equals at least 50 pages. I’ve colored coded the spreadsheet: every column in green is my initial input. Orange columns are formulas that use information from two other columns. All columns without color contain data that was entered by a virtual assistant whom I hired specifically for this job from Upwork.

Basically, I came up with the list of 50 coasters I wanted to feature in the book, then I outsourced the data collection process using Upwork. While my virtual assistant was contacting theme parks and researching data, I wrote the code to the macro that would automatically export all the data from Excel into the format in Word that I was looking for. The macro runs through a simple For..Next loop, looping through each row of the spreadsheet and exporting the data into the Word document.



After the information is exported to Word, just add your typical front and back matter, pictures, edit, and you’re done. Uploading to CreateSpace and Kindle Direct Publishing is a breeze. And you can outsource most of these tasks as well, if you have the budget for it. Or do it yourself. Most people don't realize that if you have something written, it could be on sale on Amazon.com in as little as two days. Heck, some guy got a picture of his foot to be a best seller.


Watch My Book Magically Appear


Watch the video tutorial below where I show you how to write a book fast by using Excel and see the book pages magically appear before your very eyes.


Steps to Write a Book Using Excel


Here's the basic steps I use to start writting my book with a spreadsheet:
  1. Setup Excel sheet
  2. Outsource data entry using Upwork
  3. Create macro to automatically export data to Word
  4. Run Macro
  5. Add front and back matter, pictures, etc. to book in Word document
  6. Edit and create front cover
  7. Publish on CreateSpace and KDP
  8. Market and promotion
Feel free to contact me if you'd like to know more or have questions about how the macro works to export the data from Excel into Word.

Wednesday, June 29, 2016

2016 NFL Helmet Schedule Spreadsheet

Available to download now is the 2016 NFL helmet schedule spreadsheet. You’ll see a comprehensive breakdown of every NFL pro football team's 2016 season schedule with an image of each team’s helmet design. The NFL helmet schedule is printable too. You can save the spreadsheet as an PDF file or print it out and pin up in your cubicle at work. 

2016 nfl helmet schedule spreadsheet
The Cleveland Cavaliers lifted the Cleveland curse by bringing a championship home to Believeland. Can the Browns follow it up with a Super Bowl? They've already set the date for the championship parade! There are currently four teams that have never played in a Super Bowl - can you name them all? Cleveland is one.

Stay tuned to Excel Spreadsheets Help as I'll be posting the 2016 NCAA college football helmet schedule soon.

How can I improve this spreadsheet into something you’ll use all the time during pro-football season? Maybe in the future I should also include the option to see different styles of helmets too, like these.Or maybe the Disney-inspired team logos that this guy created. What future features would you like to see?

Wednesday, May 18, 2016

How to Create folders with hyperlink from Excel Macro

I've previously shared how to automatically create folders from an Excel spreadsheet by using a macro. I recently had a reader request an additional feature: to automatically add a hyperlink from the Excel sheet to the newly created folders. I not only added this ability to my code, I also improved the overall code some as well.

The steps to use this macro to create folders is as follows:


  1. User selects range of cells they want to turn into folders
  2. Run macro
  3. Select location where folders will be created
  4. Macro automatically creates folders and hyperlinks selected cell to newly created folder


Here is a screen capture video of the VBA in action:


You can download the exact same spreadsheet template I use in the video here.

Or you can copy and paste the code below into your own Excel file. I use comments to explain what us happening on each line of code.

Sub Create_Folders()
'remember to select the cells you want to turn into folders before running the macro

'Default location where to select folder
Dim OpenAt As String
OpenAt = "My computer:\"

'Dialog box to select folder creation location
Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please Choose The Folder For This Project", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.Self.Path

'get the range of cells that were selected before the macro was run
Dim Rng As Range
Dim maxRows, maxCols, r, c As Integer
Set Rng = Selection
maxRows = Rng.Rows.Count
maxCols = Rng.Columns.Count

'---loop through all cells within selected range---
For c = 1 To maxCols
   r = 1
   Do While r <= maxRows
  
       'if the selected cell does not contain nothing, then create a folder
       If Rng(r, c) <> "" Then

           'create hyperlink in Excel file to newly created folder
           Dim cnf
           Set cnf = CreateObject("Scripting.FileSystemObject")

               'If folder already exists in this location, then just create hyperlink in Excel

               If (cnf.FolderExists(BrowseForFolder & "\" & Rng(r, c))) Then
       
               'MsgBox "folder does  already exist"
              ActiveSheet.Hyperlinks.Add Anchor:=Rng(r, c), Address:=BrowseForFolder & "\" & Rng(r, c)
   
               'if folder does not previously exist, then we need to create it and add hyperlink
               Else
               'MsgBox "need to create folder"
               cnf.CreateFolder (BrowseForFolder & "\" & Rng(r, c))
              ActiveSheet.Hyperlinks.Add Anchor:=Rng(r, c), Address:=BrowseForFolder & "\" & Rng(r, c)
               End If

           On Error Resume Next
   
       'if the selected cell contains nothing, then do nothing and go to the next cell
       End If
      
       r = r + 1
       Loop
   Next c
End Sub

Follow my Excel Help playlist for more how to videos in the future.

Monday, May 2, 2016

How To Get The Best Out of The Concatenate Function

The following is a guest post from Puneet at Excel Champs:

Text functions averagely cover  10%-15% of our Excel usage on a day to day basis. Text functions help us to present a numeric value in a simple way. If you check the below example, a single sentence is describing the performance for the September month. But we can't say there is something wrong in presenting results in tabular form. It is just a matter of requirement. Excel has lots of useful functions in this Text Function family (Upper Function, Lower Function, Proper Function etc....).

CONCATENATE Function

A function to combine text, a function to put the text together. Yes, this is the CONCATENATE Function. A Function which exactly do the same for text as SUM Function does with numbers (Giving results in a single number).

How Does It Work?

It takes the text one by one in arguments & convert them into a single string.
Syntax: CONCATENATE(text1, text2, ...text255) 
text1 is the first item to join in concatenation which is also a required argument. 
text2 ... is the additional text which you can add. You can add up to 255 items & up to 8192 characters in a single function(for Excel 2007 & Later). Each entry must be separated by a comma.
If we concatenate two strings like "Hello" & "World", then it would be like =CONCATENATE("Hello ","World"). Now if you notice we had a space between the two strings, which is quite useful while converting them into a single string. The above example is drawn by using absolute input for arguments, you can also use cell reference to add arguments.  =CONCATENATE(A1," ",B1).

How Can It Be Useful?

It can help you at a number of times.
  • Writing a conclusion for a tabular data, which will update with the changes in your data. So that, you don't have to write it again & again.
  • Converting two or more strings into a single string. So that it can make a meaning to you.

Example


Here we have an example where we are capturing the population of the major cities in the world in a string and function will be.

HURDLES

Yes, we have. But you don't have to worry.
  • Using commas to differentiate one string from another is confusing sometimes. If you skip putting a comma between the arguments, it can lead to a different result. =CONCATENATE("Excel""Champs") results in Excel"Champs. You can also put ampersand(&) instead of Comma(,).
  • The space between string is also a point to take care. You can enter space within the string "Hello " or by inserting it separately "Hello"," ".
  • If you use concatenation to join a numeric value, that value will always show in general format. That means if you have a number with 10 decimals, you will get it in the same decimal format in your string(Total growth in this month is 0.0523652). But this problem is not long lasting, you can kill it with TEXT Function & make your number just like you want.

What else to do?

Sometimes using a function like CONCATENATE is time-consuming, if you have to put it one by one different string to create a new one. You can also use a shorthand trick to get results like CONCATENATE, just ampersand(&) to join the text as do in CONCATENATE. It will give you a simple & a fast hand to join text.
Download a sample file here to see examples:
Thanks to Puneet for providing us with this explanation of concatenate. 

Thursday, March 24, 2016

2016 Presidential Candidate Comparison Spreadsheet

I'm not really too big into politics but a few readers have asked me to create a 2016 Presidential Candidate Comparison spreadsheet and here it is! This new spreadsheet serves two purposes - it allows you to see where each candidate stands on forty-eight different issues and also allows you to see how your own views align with what candidate.

1. Candidate Comparison

The first sheet in the spreadsheet is the candidate comparison that shows all five candidate's responded to forty-eight different questions on topics such as immigration, gun control, abortion, and more.


2. Which candidate should you support?

If you're still unsure of which candidate you might vote for in this election, you can use the scoring system in the candidate comparison spreadsheet to help you figure it out. Use the drop down menus to give your opinion on each of the issues - either yes, no, or undecided. Additionally, you need to decide which issues are very important to you and which ones you could care less about. Use the drop down menu to select very important, a little important, you're indifferent, or you don't care. The spreadsheet automatically uses a weighting system to score each candidate's response in relation to your opinion.


. Go to the scores sheet to see which candidate has the highest score and is therefore the most similar to you. The spreadsheet uses a "rank without ties" formula to list the candidates in order of highest to lowest score based on the weighting system.





Only 229 more days until the 2016 election! I'd love to hear what you think of this spreadsheet, if it helps you figure out your politics or not. Any suggestions to improve it?

Wednesday, March 16, 2016

March Madness 2016 Excel Brackets

It’s the most wonderful time of the year! March Madness 2016 edition is here and the first of the four “play-in” games have already begun (which we’re not counting off any points for in my office pool). Once again, I’ll be using David Tyler’s NCAA Excel brackets, the same ones I’ve been using since at least 2010, as they're still the best as far as I’m concerned. David doesn't lock or hide anything behind password protection so you can examine all the formulas to see how they work and make any modifications you desire. There are two files: the bracket manager and the individual bracket file, that can be downloaded by following the link below.


A great way to increase your Excel knowledge is to reverse engineer templates, like these office pool March Madness brackets. Last year, I shared 11 things you can learn from the best Excel brackets and they all still apply for this year’s version.

I'd like to give a special thanks to David Tyler for continuing to update and post his excellent brackets each and every March and giving us something to look forward to as we crawl out of the final few weeks of winter.

I also created a short screen capture video to show you just how easy it is to use David's brackets. Simply click on each team to advance them (don't forget to enable macros).




Pictured below is my bracket. You should probably just go ahead and treat it like the Bible, Koran, and Torah all rolled into one. It’s as good as truth.



Good luck in your office pools!

Nickstradomus


*just a special note to my email subscribers: I will hopefully have an option soon where you can opt out of notifications about sports templates, that is if you only want to read about my general Excel tips and tricks.

Sunday, February 14, 2016

2016 NASCAR Fantasy League Manager Spreadsheet

2016 is the third year in a row that I’ve made the NASCAR Fantasy League spreadsheet available for download. This NASCAR fantasy game is based on Total Driver Points according to NASCAR’S scoring system. In order for your drivers to score they must be running for Sprint Cup Points. Drivers may run in more than one NASCAR division but can only score points in one division.


How to Play NASCAR Fantasy in Excel

At the beginning of the year (first race is Daytona on February 21st) participants choose ten race car drivers. These will be their 10 drivers for the entire year - no changing after the entry deadline. All players also submit the three drivers that they think will place 1st, 2nd, and 3rd in the NASCAR standings at the end of the year. They don’t need to have these drivers in their picks, just who they think is going to finish on top. This is used as a tie-breaker.

The fantasy league manager manually enters each player’s picks into the spreadsheet at the beginning of the year (or uses the new entry form to copy and paste entries). Players keep the same drivers all year long. After each race, the manager then goes to ESPN.com and enters the amount of points that each driver earned during the race into that driver’s column. The spreadsheet then automatically adds and ranks each player according to their driver’s scores. I used many of the same formulas found in the leaderboard of my NCAA Bowl Prediction Pool sheet. This Nascar fantasy manager template can currently handle up to one hundred drivers and one hundred participants without needing to modify a single formula!

This year, there are two options to download the 2016 NASCAR Fantasy League manager:
Download Option 1 (no info require):

Simply click the link below which will take you to the box.com, where the file is hosted. Next, click on the “Download” link in the upper right hand corner. That’s it! No email address is required.

Single Player Entry Form.xls

Download option 2 (email address required):


File is hosted on Gumroad. Click "I Want this" then submit your email address to download the spreadsheet. Your email address will ONLY be used to alert you if I upload a new version of the file, either an update for this year or you will also be notified when next year's sheet is ready to go.

https://gum.co/nascarmanager

Why two links? Sometimes users find small bugs or offer suggestions for improvement and so I update the spreadsheet. I recommend downloading through download link 2 so you will be automatically notified when an update is made, whether for this year’s spreadsheet or next. But if you don’t feel comfortable submitting your email address I understand, which is why download option 1 is available.

I’ve received comments from many readers informing me they like to use these types of sport manager templates to raise money for their favorite charities, which is so awesome! In the future. I will improve the spreadsheet by automating the entry process (I know, been saying that for two years now) and come up with a way to update the driver scores easier. This is just the third iteration of the template and I don’t follow NASCAR myself like I do football, so I’m sure it can be improved in some areas. Please let me know if you have any suggestions!

Tuesday, February 9, 2016

Excel Help Milestone: 1.5 Million Page Views!

In my 7th year of blogging, I’ve reached another monumental milestone. In April of 2014, I surpassed one million page views. Today, I am excited to announce Excel Spreadsheets Help has surpassed the 1.5 MILLION page view mark. Yes, I realize there are larger websites out there that may have 1.5 million views in a single day, but those are larger brands with an entire team or company of people. This Excel help blog is run by one person in his free time using Google’s free Blogspot blogging platform, so to me it’s quite the accomplishment. And it’s hard to wrap my mind around 1.5 million of anything. That’s like 15 Ohio Stadiums full of scarlet clad football fans. Wow!

Now, let’s take a look at some fun numbers and statistics from seven years of sharing Excel tips and templates:
  • Excel Spreadsheets Help Age: 7 years
  • Total number of posts published: 248 (249 after this one goes live)
  • Time to get to 1 million page views: 53 months (2009 - 2014)
  • Time to go from 1 million to 1.5 million views: 21.5 months (2014-2016)
  • Total time to hit 1.5 million: 74.5 months
  • Estimated timeframe to hit 2 million page views: November 2017
  • Average number of posts per month: 3
  • 1.5 million views / 74.5 months = 20,134 average pageviews per month
  • 1.5 million views / 248 posts = 6,048 average pageviews per post


The top five most popular posts since December of 2009 are listed below and interestingly, all top five posts were written in 2010:

Sunday, January 31, 2016

5 Lessons Learned from Super Bowl Squares 2016 to Improve Your Excel Skills

Last week I released the latest and greatest version of my Super Bowl squares game template with more ways to play than before. Even if you don’t care for football or sports, numerous lessons can be learned by dissecting this spreadsheet, including:
  • How to assign a macro to a command button
  • How to clear cells with a macro
  • How to generate random numbers in Excel with a macro
  • How to use a macro to create a list based on cell values
  • How to create a dynamic drop down list
I thought I would offer a few more details and insights into how each of these features work.

1. How to assign a macro to a command button

To insert a command button into your Excel spreadsheet you’ll want the Developer tab to be available. Go to File > Options> Customize Ribbon and under Main Tabs check Developer. Now at the top of Excel you should see the Developer tab. Click on the Insert button drop drown, then under form controls click Command Button. Place the button wherever you want along with the desired size. To have a macro run on the click of the button, right click the command button and select Assign macro. Pick your macro and you’re good to go!


2. How to Clear a Range with an Excel Macro

To clear a range with an Excel macro, use the code below. When you click the “Clear Numbers” button, it is linked to this macro and will clear the range.

Sub Clear_Random_Numbers()
'clear the random numbers
Dim DelRange As Range
Set DelRange = Range("B5:B14")
DelRange.ClearContents
End Sub


Unlike a formula, if you insert a row or column and your target range moves, the macro will not update automatically like a formula will. Keep this in mind when making changes to a spreadsheet containing a macro. It’s a good idea to have the layout and format fixed as much as possible before writing your macro code.



3. How to generate random numbers in Excel with a macro


To generate random numbers in a range with a macro, modify the code below. This code generates random numbers between 0 and 9. To start from 1, take out the “-1”. To get more random numbers, change 10 to 100 or whatever value you need.

Sub Generate_Random_Numbers()
'define range of cells for NFC random numbers
Dim NFCRange As Range
Set NFCRange = Range("B5:B14")
NFCRange.ClearContents
For Each c In NFCRange
Do
c.Value = (Int((10 * Rnd + 1))) - 1
Loop Until WorksheetFunction.CountIf(NFCRange, c.Value) < 2
Next

4. Macro to Create a List Based on Cell Values


The next macro for the Super Bowl squares sheet is for the Assign Names Randomly sheet. This style of play allows users to enter their name then input how many squares they want. So we need to generate the complete list of names. The key to this program is getting the row numbers to add correctly.

Nick 3
Sarah 2
Henry 1

List would look like this:

Nick
Nick
Nick
Sarah
Sarah
Henry

How do we get the result? Use loops to repeat the code that adds the name to the list.

Sub Assign_Random_Number_For_Names()
'stop screen updating so macro runs faster
Application.ScreenUpdating = False
'clear the range
Dim DelRange As Range
Set DelRange = Range("AA2:AA101")
DelRange.ClearContents

'generate a list of all the names in order to populate the field
Dim row As Integer
Dim NumberSquares As Integer
Dim Squares As Integer
Dim RowNum As Integer
Dim PlayerName As String

RowNum = 2

For row = 2 To 101

NumberSquares = Excel.Cells(row, 24)

For Squares = 1 To NumberSquares
PlayerName = Excel.Cells(row, 23)

Excel.Cells(RowNum, 27) = PlayerName

RowNum = RowNum + 1

Next

Next

5. How to create a dynamic drop down list


In this spreadsheet you may have also noticed I used a dynamic list – a drop down list where the values change based on other cell’s value. There are only one hundred squares in Super Bowl squares, so when each player picks how many squares they want, the total value can only add to one hundred. When making your selection it should only show how many squares remain, basically it needs to subtract from 100 the total value of squares already entered. This is done in column P using an IF and ROW formula. If the total value of squares taken is less than one hundred, then show the row number, if not, show nothing.



I hope by examining this template you’ve learned a thing or two about Excel.