Con Dividere

Main Menu

  • Normal Value
  • Quota By Country
  • Statement of Financial Accounting
  • York Antwerp Rules
  • Commerce

Con Dividere

Header Banner

Con Dividere

  • Normal Value
  • Quota By Country
  • Statement of Financial Accounting
  • York Antwerp Rules
  • Commerce
Normal Value
Home›Normal Value›When the Excel sum is wrong – Manila bulletin

When the Excel sum is wrong – Manila bulletin

By Thomas Heikkinen
October 6, 2021
0
0

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


Source link

Related posts:

  1. It’s Time to Rethink Your Global Logistics
  2. Xtra-Gold Sources Pronounces 2021 Regular Course Tender Provide and 2020 Provide Outcomes
  3. The brand new assortment from Payal Singhal is brilliant, daring and primarily based on the brand new commonplace
  4. 2021 Lexus ES250 AWD First Take a look at: Accessible Luxurious

Recent Posts

  • FENNEC PHARMACEUTICALS INC. Management report and analysis of the financial situation and operating results. (Form 10-Q)
  • Stocks rebound after inflation moderates slightly, giving investors hope that prices may have peaked
  • Shipping industry reports 54 losses in 2021 amid challenges: Allianz
  • Once-Abundant Atlantic Cod Trait Hits New Low
  • Metro Bus restores express lanes and adds free service to parks | Local News

Archives

  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • July 2021
  • June 2021
  • May 2021
  • April 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • March 2020
  • April 2019
  • June 2018
  • May 2018
  • June 2016
  • May 2016
  • November 2015

Categories

  • Commerce
  • Normal Value
  • Quota By Country
  • Statement of Financial Accounting
  • York Antwerp Rules
  • Terms and Conditions
  • Privacy Policy