7 Replies Latest reply on Oct 1, 2018 8:13 AM by meenu choudhary

# Help with formula

Hello everyone,

I have a chart that displays the number of records in each status type.  There are only 2 status types, "new" and "accept".  I want to display a 3rd line on this chart that is the number of accept records by the number of new records.

I cannot figure out how to write a formula using "number of records" but only of a certain status type like this

Number of records where status = accept / number of records where status = new

Any help is appreciated! thank you • ###### 1. Re: Help with formula

create 3 calculates:

1 field - accept

if status = 'accept' then number of register end

2 field - new

if status = 'new' then number of register end

3 field

sum (accept) / sum (new)

Regards

• ###### 2. Re: Help with formula

Without looking sample data or workbook I'd like you try something like this,

SUM(IF [Status] = 'Accept' THEN [Number of Records] END)/SUM(IF [Status] = 'New' THEN [Number of Records] END)

• ###### 3. Re: Help with formula

Hi Richard,

SUM(if status = "Accept" then [number of Records] end)/SUM(if status = "New" then [number of Records] end)

The above calculation will do

Hope this helps

Plz mark this answer as correct/helpful to close the thread

BR,

NB

• ###### 4. Re: Help with formula

Hello,

Sorry it has taken me so long to respond to this.  For some reason I cannot get this calculation to work, it is just returning blanks.

The packaged workbook is attached.  I am working off the Conversion (2) tab and the new field I created based on the above is "Calculation 1".

ATTACHMENT REMOVED AFTER CORRECT ANSWER WAS RECEIVED

• ###### 5. Re: Help with formula

Hi Richard,

You can try wrapping the logic into "ZN" function:

ZN(SUM(IF [Status Type1] = 'Accept' THEN [Number of Records] END))/ZN(SUM(IF [Status Type1] = 'New' THEN [Number of Records] END)) • ###### 6. Re: Help with formula

Wow! That did the trick....why does that work? (so I can learn )

• ###### 7. Re: Help with formula

For the below logic, when [Status Type1]= 'Accept' then  [Number of Records] else NULL.

So, When you are wrapping this logic to sum , it will try to  [Number of Records] with null, and NULL operation with any value is null.