Formula Tool - Subtract Fields Question

[jfxh]

Hi! Just a quick question, it seems exceedingly basic but I cannot find anything from searching the community.

Basically, I have a formula tool that creates a new Column. If the relevant fields of the incoming data [Sum_Cash Amt], [Sum_Revenue Amt], and [Sum_GST_IN Amt] is true in the following fashion:

-[Sum_Cash Amt] = [Sum_Revenue Amt] + [Sum_GST_IN Amt]

OR

[Sum_Cash Amt] + [Sum_Revenue Amt] + [Sum_GST_IN Amt] = 0

Then for that row, I want it to return "Risk Criteria 1"

However, I cannot get either to work (only need one of the above, so no need for an actual OR formula, methinks). Even when I export into excel and add up the fields, they equal zero. However, the formula returns a null, as per the formula below:

If -[Sum_Cash Amt] = [Sum_Revenue Amt] + [Sum_GST_IN Amt] Then "Risk Criteria 1" Else null() EndIf

I would not expect this to be the case, and hence am confused why it is not returning "Risk Criteria 1" for most of the rows. For an example, see Row 13 of the formula tool's output - although the figures add together to be nil/equal, "Risk Criteria 1" has not been returned.

Thanks!

[nerces]

Can you please post a copy of the Excel sheet. Or at least a sample of it.

[jfxh]

Hi Nerces,

Here you go - sorry about that.

The excel sheet itself is from another work flow, which is a lot larger. Let me know if you need that too.

[nerces · Accepted solution]

The issue is to do with your datatypes and rounding. Here is a solution. It's not the best solution but it should guide you to recognising where you went wrong.

[jfxh]

Hi Nerces,

Thanks for you help - it works great.

Out of curiosity, when I tried to use the select tool to convert all the fields into Fixed Decimal with 19 scale and 2 precision, and then kept my original formula it did not work like your solution did (for example, for those where the total was a small non-zero value i.e. 0.00000000001 it did not return "Risk Criteria 1"). 

By the sound of it, I cannot convert my fields to Fixed Decimals first, instead I need to calculate with fixed decimals as my output format and then use the formula to assess whether it is nil or not. Is this correct?

[nerces]

Yeh, it's a bit weird. Usually you have to convert it all to however many decimal points you want and then the output needs to be in the same format otherwise you get really small value in scientific notation that don't technically equal 0. You can always round those values to 0 decimal places to get an integer 0 or to 2 decimal places to get 0.00. I do apologise if some of this doesn't make sense or confuses you as it's not the easiest thing to explain over text.

Edit: I've attached a cleaner version of my workflow to this comment.

[jfxh]

Thanks for that - I actually didn't notice you had changed the format on the multi-formula tool as well to make it fixed decimal. Will keep in mind in the future. Cheers!