This is the first post in Penn Foster’s Analytics Blog Series, which seeks to demonstrate how organizations can utilize the power of analytics and data to improve business outcomes. Authors Daniel Swimm and Taylor Halsted are leaders on Penn Foster’s Strategy & Analytics team and between them bring a wealth of knowledge in statistical analysis, forecasting, and data modeling. Look out for future posts in the series in the coming weeks.
Often there are times when excel can be overly intimidating with hundreds of unknown formulas and the computing horsepower of a well-oiled Lamborghini. Even though many people say that the two of us were born with a spreadsheet in our laps, we know how it feels to open up the program and not know where to turn for help. Being a self-taught excel pro is much easier than you think and we have highlighted some of our favorite tips in this first part of our Analytics Blog Series.
Tip #1: The Three Amigos- SUMIFS, COUNTIFS and AVERAGEIFS
These are some of the most used formulas by both of us in our daily operating models. Being fairly easy to understand and manipulate we expect you to be able to incorporate these formulas into your tool kit right away.
These formulas are used when you would like to sum, count or average a column of data with more than one condition. For example, we can use the SUMIFS formula if we would like to know how many enrollments School C has in the state of Texas from the below data set.
Likewise, we would use the AVERAGEIFS formula if we wanted to know the average number of enrollments from School A in New Hampshire, or the COUNTIFS formula if we wanted to know the number of School B locations in Idaho.
Tip #2: Index Match Formula
This is hands down our favorite. We use this formula when we would like to look up text or a number from a list. For example, we have a list of school names built in our pivot table and we would like to bring over what state that school is in from one of our lists in another sheet we would use the index match formula. MAJOR KEY TO SUCCESS- make sure you include a zero at the end of the match formula for the match type, if not you will receive and error.
Tip #3: EOMONTH Formula
A pretty simple yet very powerful formula that can give you the last day of every month. For example, if we were building a table and wanted to have the 12 months of the years going across the top of our sheet this would be an easy way to give us the last day of every month. Simply put your starting date in a cell and then write in the EOMONTH formula in the cell next to it. You can chose how many month from the start date you would like to go after you have chosen the start date in the formula. You can see that I have chosen 1 month from the start date below.
There you have it. Three of our favorite and most used excel tips that should have you fired up and ready to become your company’s in house excel guru. The next time you are at the water cooler getting a drink and someone says, “Hey there’s the data guy/gal” don’t look over your shoulder because they will most likely be talking to you.
Resources: Photo Credit