formula issue

[mjtowne]

Hi,

I am trying to work out how to solve an issue i have with obtaining the correct whole number based on two fields Example data below

Field A     Field B        Result Expecting

1                 1.411                    1

1.351              2                       2

1.023           1.87                     1

1.52             1.97                     2

So i am trying to create a calculated field that will do the following.

1. first rule check field A is an integer if yes use this if not use field b if this is an integer

2. If the first two dont meet then it will check within 5% of a whole number ether side closest to a whole number. 

Hope you can help i can do rule one it is just the 2nd part i am trying to understand how to extract the decimal.

thanks

Martin

[jdunkerley79 · Accepted solution]

Think a formula like:

Abs([A]-Round([A],1))/Max(1,Abs(Round([A],1)))

will give you the percentage difference from the nearest integer (treating 0 as 1).

Using this you can pick between A and B or null if all outside 5%.

Attached a workflow to show what I mean