by ·0 Comments

String Manipulation using Microsoft Excel Text Functions
String Manipulation using Microsoft Excel Text Functions

     Last month I had an interesting Twitter exchange with Microsoft Excel’s Social Media team. @MSExcel asked it’s followers to share an Excel function or formula that they felt most proud of mastering. Many responses included rather complex formulas involving a clever combination of different functions. Yet, my example simply listed Excel’s MID function. When asked why I used such a basic example I reiterated a personal belief that Excel’s text manipulation functions are greatly underused and consequently underrated. Surely, VLOOKUP function is probably the most in-demand Excel function, soon to be replaced with a much anticipated upgrade: XLOOKUP ; yet many Excel users simply overlook or don’t realize the power and usefulness of Excel’s text manipulation functions. Whether you need to combine multiple strings together, or reformat your case-sensitive text, or perhaps parse out text without using Text to Columns tool , or even remove unwanted text, or find precise position of a specific letter or word, or replace old values with the new ones, or calculate number of characters in your string; all of these tasks can easily be accomplished with the help of mighty Excel text functions. An icing on the cake is the fact that these functions are very easy to use as long as you’re aware of their existence. In this post we will cover following text functions: UPPER, LOWER, PROPER, LEN, LEFT, RIGHT, CONCATENATE and already mentioned MID Excel functions.
Continue reading

by ·0 Comments

Learning to use data visualization programs


Gartner Magic Quadrant for data visualization programs

          Imagine spending countless hours analyzing your data and finding a meaningful insight that can help shape direction of your business – the only missing piece is convincing functional stakeholders that your analysis is in fact valuable. In this article we will discuss learning resources offered by leading tools that help us communicate our data findings. In fact, no analysis no matter how thorough and complex it might be could yield any real value if the compelling recommendation is not provided to help company leadership take further action. This is exactly where data visualization comes into play: this software allows us to connect to disparate data sources, wrangle our data, perform ad-hoc analyses, distill a powerful data story and even build dashboards to enable further analysis by other folks in our organizations.

          According to Gartner’s Magic Quadrant for Analytics and Business Intelligence Platforms (1) the list of leaders in the data visualization fields include Microsoft Power BI, Tableau and Qlik . In an apparent effort to grow their user base and democratize data analytics, all of these providers offer fully functional versions of their programs free of charge or in case of Microsoft with an affordable subscription plan. Even better, these vendors formalized their knowledge base via various free online learning options.
Continue reading

by ·0 Comments

Coding a BMI Calculator in Python


Coding a BMI Calculator in Python

         My Java Script BMI Calculator post, which was written a little over three years ago still generates a decent amount of traffic. We’ve also previously covered writing a VBA code for this calculation. Today we’ll try to refresh our programming skills and take a stab at building a Python version of this code. There are some great reasons why Python is overtaking R in becoming the leading programming language for data science projects. Before we delve into any data analysis type of exercise, I thought it would be helpful to get orientated to this language via easy to follow calculation: BMI = (Weight/(Height^2)) * 703.06957964 , where Weight is measured in pounds , while Height is in inches . Before we write the first line of code, one thing to keep in mind is that while Python is a fairly easy programming language to understand and follow along; it’s rather peculiar as far as indentation is concerned. Please exercise a due care to ensure that your code compiles successfully by eliminating all of the extra blank spaces in your code and following proper indentation rules.
Continue reading

by ·3 Comments

Working with Google Sheets’ QUERY Function


Using QUERY Function in Google Sheets

          So far we’ve covered some ground when it comes to using spreadsheet functions within MS Excel ecosystem. Perhaps it’s time to turn to one of its leading competitors: Google Sheets ? When GoogleSheets was launched noone, but perhaps its creators would seriously consider it to be a real threat the mighty, ubiquitous Excel spreadsheet package. Over the years this product went through a plethora of incremental, as well as break-through enhancements. By now GoogleSheets achieved an admirable user base backing it’s strong functionality matching and in some instances surpassing the gold standard continuously maintained by the much beloved Excel program.

          One of most versatile Sheets’ functions is the QUERY function. Its SQL-like syntax allows us to retrieve specific information from our worksheet, enabling various filtering, sorting, lookup, and aggregation capabilities. While this function is more powerful than INDEX/MATCH, VLOOKUP, SUMPRODUCT, SUMIFS and others, its syntax is rather simple: QUERY(Data, Query, [Headers]) .

Data – range of cells we want to query from

Query – actual query code, to be enclosed in quotation marks or be a reference to a cell containing query text.

Headers – OPTIONAL, number of header rows at the top of data, when omitted value is set to -1.

          If you can comfortably compose basic SQL queries, you will find this function rather intuitive and easy to use. However, you might also find yourself wanting to throw your keyboard across the room when some of the SQL functionality we take for granted is either not found or not implemented properly in this function. Working with aliases has definitely topped my personal list of annoyances.
Continue reading