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

# Revenue Bins Formula - not counting correct values

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.

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

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...

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

• ###### 1. Re: Revenue Bins Formula - not counting correct values

Couple of things, you need to Extract your data before saving as a packaged (twbx) workbook (as is we can't open your workbook without signing onto your server).

Also you don't need any of those AND statements in your Revenue Bins formula. If it's greater than 10k it will be assigned the bin and then exit on line 1. If it's greater than 5K it will be assigned the bin and then exit. Adding the AND doesn't do anything because nothing greater than 10K is ever going to get to the 5K line.

Repost the workbook and we'll take a look.

--Shawn

• ###### 2. Re: Revenue Bins Formula - not counting correct values

Hi Shawn,

Thanks for the clarity. See attached workbook with the extracted data.

Notice, as I mentioned earlier above - whenever I extract the info, the formulas seem to be working fine. I was hoping to duplicate the results in this extract below, in the live connection above.

Best,
Karan

• ###### 3. Re: Revenue Bins Formula - not counting correct values

Sorry I didn't pick up on that part of your question. This sounds to me like a support case.

Cheers,

--Shawn

• ###### 4. Re: Revenue Bins Formula - not counting correct values

Aw man. Okay thank you Shawn very much!