Excel Snippets - Grouping people by age groups

Every once in a while, every excel user would need to group people in a list by age group. The prerequisite for this task is either an age column, or date of birth (DOB).

In case you have the DOB column. the first step is to add and populate the age column. the quick and easy solution is using the following formula:

 =INT(YEARFRAC(birthdate, TODAY()))

Basically, this formula fetches the integer part of year fraction of difference between the birthdate cell and today's date.

Now comes the actual grouping part. This approach assumes a fixed number of years groups and an upper max (optional). For example, we may need to group from 1 to 15, 16 to 30... and so on till 61 to 75, and finally 75+ (everyone older than 75 years). To achieve this, add another column to the table and name it "What was I thinking?" or anything you feel like. Then add the following formula assuming  H2 is the first record cell for Age that you got from the formula above:

=IF(H2>75,"75+",INT((H2-1)/15)*15+1&"-"&(INT((H2-1)/15)+1)*15)

Copy this formula to all the other cells in the Age group column using the auto-fill feature of excel, i.e. double clicking on the lower right corner of the original formula cell (where the cursor changes to a +). It should automatically convert the formula reference of H2 to the corresponding row numbers like H3, H4, H5.....

A little explanation of what happens in the formula, in case you are one of those who really need to know:

1. H2 is the cell address from where age value is picked up. 
2. H2>75,"75+" adds 75+ to the group column if the age is greater than 75
3. If H2 is not greater than 75 then the second part kicks in.
4. INT((H2-1)/15)*15+1 calculates the lower limit of the 15 years interval in which H2 falls.
5. &"-" adds a hyphen after the lower limit value.
6. (INT((H2-1)/15)+1)*15) calculates the upper limit of the 15 years interval in which H2 falls.

Remember, we decided in the beginning that we want to group into 15 years intervals with an upper limit of 75+. You may modify the formula to any other grouping simply by changing the 75 with whatever you want the upper limit to be, and replace all the 15s by the group intervals in years. For example, if you want to group into 5 years intervals with an upper limit of 55 years, your formula would be:

=IF(H2>55,"55+",INT((H2-1)/5)*5+1&"-"&(INT((H2-1)/5)+1)*5)

So there you go, age grouping conquered, next we move onto adding a wildcard search term in the IF statement to identify columns containing a certain text string anywhere within the cell contents.

Business Intelligence - The Intro

BI Dashboard examples (Click to Enlarge)

Business Intelligence does not have a formal definition but the term is used for a variety of tools and activities used to analyze raw data and present it in a more meaningful and useful format for business analysis purposes.

The term Business Intelligence has been abbreviated to BI and is used alternatively. We will stick to using the abbreviated form BI for representing Business Intelligence in all our Articles, Blogs, Guides and Forums.

BI provides current performance status in relation to past performance and predictive  future views, and provides the target users (business leadership) the ability to modify (slice and dice) and dive deeper (drill down) or summarize (drill up) information. The data becomes even more useful when a comparison to industry specific benchmarks is incorporated.

BI helps organizational leadership to make informed decisions and take calculated risks in both operational and strategic spheres. KPI's can be established and performance can be measured against those. Performance Indicators breaching preset thresholds utilizing easy to read and at-a-glance visuals, can lead to further digging (slicing/dicing and drilling) into the data.



BI information is generally represented as Dashboards, which are collections of related visuals and reports, providing an at-a-glance . Raw data is converted into an OLAP cube. OLAP stands for Online Analytical Processing, which may be defined as a computer based technique for analyzing data and providing insights. Cube is used to define a mutli-dimensional data sets. traditionally a data set has two dimensions , rows and columns. OLAP cubes have more than two dimensions and those with more than three dimension are also sometimes refer to hypercubes. The advantage of cubes is the data is pre-processed and readily available for slicing, dicing and drilling, saving time compared to running queries in real-time against traditional relational data sets.

NULLs in SQL results Export to EXCEL

I was working on a project to export some data into a tab delimited file. Initially, we did some test files and it was simple enough to use a select query and export the results to a text file by right clicking the top left box of the results pane. However, the recipient indicated that the file had NULL columns with the text string 'NULL' in them which interfered with their processing. They requested us to remove the NULL string and instead pass on these fields as blank. I found a free tool called FnR, for Find and Replace, which replace text strings with whatever value is specified. It was all working fine till they asked us to send a full file with more than half a million records. FnR refused to work and would time out. I generated a command line command, another useful feature built into FnR, and tried that, but no joy. So i went to my trusted friend Google and asked for solutions. I found that this has been a very common issue and many people had asked for a solution, only t get lectures on what they are doing wrong and long debates of what DBAs and Analysts  deemed best practices. Some workable solutions were also discovered in the process, but when dealing with a large number of fields it becomes impractical to use REPLACE or COALESCE commands with every field to check if it contained a NULL and then replace it with blanks. While the debate lingered on from forum to forum, the person asking the question would eventually bail out due to frustration without getting a proper solution to their problem. I did however came across one suggestion that wasn't given much attention of using the query to export the results to text, rather than the right click process I was following. I decided to give it a try and, viola, no more NULL strings.  

MS Excel with phantom contents

This morning I came across a strange issue of not being able to view the contents of certain cells in an Excel spreadsheet. the selected cell did display the contents in the formula bar, but the cell itself showed blank. Did some quick google search and found others had faced similar issue. There were a few suggestions and seemed to work for some. It included selecting the cell / row and pressing crtl+B, essentially formatting the text. This worked for majority of the users with this problem. However, didn't work for me. Another suggestion seemingly working for a few others was to go to the View tab and selecting Arrange All>Tiled>OK. That didn't work for me either. Finally what worked for me was to selected the cells with invisible text and changing the font to another font. The issue seems to be with a corrupt font on your system. Well there you go, one of these solutions should work, please let me know if they didn't, thanks.