What do you do when you have data, but it's not in the format you want or it's missing information?
While Excel has a ton of built-in functions like Sum, Average, VLOOKUP, there are times when they aren't enough to solve your particular problem.
Before you give up on your project or resort to manually calculating each data point, there are ways to solve your challenge with some advanced Excel features. For example, user defined functions (UDFs), which act like built-in functions, can be customized to perform a particular action. In the example to the right, we created a UDF called "ConvertName()" that takes names in different formats and automatically outputs the first and last name. This UDF can be applied to any list of improperly formatted names.
While this is a simple example of a customized formula we can create for you, UDFs are a very powerful feature of Excel that can solve your data challenges quickly and accurately.
You have a website, but how do you determine whether it's attracting your target audience? Specifically, how do you know from where and how often people are viewing your site? The server hosting your website stores visitor data in a log file, but it is nearly impossible to use the file, as is, to derive any useful information.
There are software packages that analyze website log files, but they don't always answer your questions. With a programming language like Python you can extract, transform, and present the data exactly how you need it.
This challenge can be solved in three steps.
With a click of a button, we can now visualize the origin of our visitors, the frequency of their visits, and the date of their last visit. This just scratches the surface of what is possible with Python, and how we can slice and dice data to extract the information you need to make important business decisions.
Do you waste time on repetitive tasks like formatting, manipulating, and calculating data? For this project, the client was manually calculating the cost of an injury over a person's lifetime and re-formatting the output each time.
While Excel templates with formulas can be useful, this particular challenge needed something that was much more flexible and customizable. In particular, it needed to handle variable cost inputs and be used for different clients.
Excel macros are written in VBA code that can automate a manual sequence of steps and output the data exactly how we want it. In this example, we created an input sheet to plug in the variable cost factors. We then wrote a macro to perform the manual calculations and output the customized analysis sheet. By simply clicking a "run" button, the analysis was complete in seconds. To enhance the solution, we added error checks to make sure the input data was correct and formatted properly. As in this example, Excel macros can save you time and headaches by automating common, repetitive tasks.