Jump to content
  • 0

The formula seems to be correct but returns 0 values




I hope someone can help me with this one.  I need to calculate 3 ratios here: Success Rate, Cancelation Rate, and Failure Rate. To do so, I calculated values of New, Canceled, Completed, In Progress, and Trashed using SELECT COUNT and those values are correct. Then, I calculated the Total by summing them; that value is correct too.

(([@calcfield:3]+[@calcfield:1]+[@calcfield:2])/[@calcfield:6])*100 - This is the formula for Success Rate. It is (New + In Progress + Completed/ Total)*100

([@calcfield:4]/[@calcfield:6])*100 - This is the formula for Cancelation Rate. It is (Canceled/Total)*100

([@calcfield:5]/[@calcfield:6])*100 - This is the formula for Failure Rate. It is (Trashed/Total)*100.

Everything was working fine as, I had only In Progress, New and Completed projects in my Projects table. Here is the screenshot of it.


When I added the test project whose status was Canceled to test this, the rates were incorrect.


Does anyone have any idea why this could happen? When I added the test project which status was New everything was correct again. It looks like there is an issue with formulas when I add the Canceled or Trashed project only, but I cannot figure out why. Thank you all in advance for the support.



Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

Hello @Aleksandra,

The COUNT() function returns a value that has an Integer data type.

The Total is the sum of values returned by The COUNT() function, so Total is also an Integer.

In calculations when all the arguments are integers the result is an integer too.

For example: Success Rate in your Example#2 (3+ 1+ 4)/ 9 = 0.88, however only the integer part of the result is returned and it is 0.

To solve this one of the arguments should be converted to the Number (so, the decimal part is required).

For example, in Total you can change the formula: ( New + In Progress + Completed + Canceled + Trashed) *1.0

In this case ratios will be calculated correctly. If needed use the Formatting option to round the results, etc. 



Link to comment
Share on other sites

  • 0

Hi @CoopperBackpack,

Thank you for your response. I had all of my values (including arguments) converted to numbers, but I put 0 for decimals there as they will always be round numbers (integers). I was thinking not having decimals might be an issue, so I put 2 decimal places for them as well, but it didn't work.

What worked is your advice to multiply the total value by 1.0.  I got the correct values now, so it is resolved.  Thank you so much.




Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...