4 Replies Latest reply on Feb 9, 2016 1:15 PM by Karan Parekh

    Revenue Bins Formula - not counting correct values

    Karan Parekh

      Hi All,

       

      let me start off by saying this is the wierdest scenario that I have ever been in.

       

      Procedure: Created a revenue bins formula (IF STATEMENT) to create bins that count companies based on their total sum for each quarter of the year.

      i.e. if sum > 100 THEN 100 - 500 etc...

       

      Problem: For the most part, the bins formula works by counting companies that spent greater or in between what the formula dictates. Pretty straightforward, thank you Pooja Gandhi for helping me on this. 

      But there are about 5 - 10 values that are not being counted correctly. See images below. The first three rows are obviously incorrect.

      image.png

      Notice, it is giving a value of 1 (count distinct) even though parent 1685 HAS not spent more than 10K.

      Capture.JPG

      It is counting 87 companies that spent at least 10K in Q1 - the correct answer should be 84, not 87.

       

      Now here's where the weirdness begins.

      I set up a packaged workbook through an extract so that you can directly access my problem. I extracted on the time frame I was looking at (2015) and the team i was looking at (central DE). The formula picks up everything and works perfectly.

      Here is what the output looks after I extract and package the workbook - this is correct!! The companies are correctly being labeled...

      Capture.JPG

       

      Goal: I don't care about why the extract is working. I want to know why in my original workbook, my formula is counting companies as 10K+ even if they actually aren't so.

       

      I have attached a workbook. I hope some of you may be able to help me.

       

      Best,
      Karan