Or why data analysis depends on data preparation.

I wanted to find the “Reach” of some Facebook groups. In my mind, it should have been a simple SUM calculation. This should were a simple formula using Excel’s Sum function. We can add all the values ​​of “# of members” to get a total. So I open the list of FB groups of my staff in XLSX format. And boy, I was wrong!

Column A contains the name of the FB group (anonymized) and column B contains the number of members for each group. This is shown below:

Your eyes immediately detect the fact that the numbers are in different formats. Some numbers are expressed in Ks (1000s). Other numbers are expressed as normal numbers. I have highlighted them in red.

Apparently, Excel cannot summarize the values ​​in column B. Excel does not know that K means 1000. Thus, the sum of the number of members (Reach) cannot be calculated correctly. We would get an incorrect value of 229,949 members if we were to sum without processing the values ​​in this B column.

This formula converts the “K” to 1000. This means that 14.6K will become 14600. We use the Find and LEFT function for this formula.

= IF (FIND (“K”, B2)> 0, LEFT (B2, FIND (“K”, B2,1) -1) * 1000, B2)

This formula searches for the letter “K” in the value in column B. If K exists, the result of Find (“K”, B2) will be greater than 0. If true, returns the original value of B2 without the K (14.6), then multiply it by 1000 to get 14,600.

However, if the K is not in the value in column B, just return the value in column B. For example, FBgroup13 has a value of 229100 (no K). Its value should therefore contain 229100. And voila! We are now getting somewhere!

We haven’t finished yet. While the “translated” values ​​of B2 are correctly listed in column D (column1), you see that for values ​​in column B without “K”, the formula results in an error #Value! for the translated value column. I have also highlighted them in red.

Look at FBGroup13 should be 229,100 and the value for FBGroup15 should be 849. But both get #VALUE! instead of. We solve this by creating a new column E (column2). Here we test for the error condition.

= IFERROR (d2, b2)

If we get #Value! (or any error) just get the value of B2, otherwise use the existing value of D2.

Now that we have converted the values ​​to the correct numbers, we can easily add up the total number of members for all FB groups. The correct number is 448,749.

SUBSCRIBE TO THE DAILY NEWSLETTER

CLICK HERE TO JOIN