Identify Non Whole Numbers

[reginawhelan]

Hi, I need help with a formula. I need to identify any cells that do NOT have a whole number. Any suggestions? 

[atcodedog05]

Hi @reginawhelan ,

Here is how you can do it. Use this formula.

if ([Field1]-Round([Field1], 1))=0 then 'Whole'
Else 'Not Whole' Endif

Output:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀

[reginawhelan]

Hi 🙂 Thank you but I'm not seeing the desired results with this formula. I will explain a little...

Formula #1 divides two numbers from two columns, I changed the Data Type to 'FixedDecimal - 19.2'. 

Formula #2 is your suggestion but I added an IsNot Empty as I don't want an error produced if the cell is NULL.

IF !IsEmpty(([Field]-Round([Field], 1))=0) THEN 'Whole' ELSE 'Not Whole' ENDIF

One of the numbers from formula #1 is 0.7 and should be identified as NOT WHOLE but the above formula indicates that it's WHOLE.

Do you know what the problem could be?

[atcodedog05]

Hi @reginawhelan 

Sorry for the mistake. I rectified the issue. Try this.

if ([Field1]-FLOOR([Field1]))=0 then 'Whole'
Else 'Not Whole' Endif

Output:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[atcodedog05]

Hi @reginawhelan 

The issue was with round for number greater then 0.5

Floor function fixes it.

[reginawhelan]

😞 still not working. Could it be because there is a previous formula calculating this number?

[atcodedog05 · Accepted solution]

Hi @reginawhelan 

Output the calculation to a Column and then check Whole or Not Whole. And numeric function is allowed on NULL it doesnt create error. You can use the below formula to flag NULL

if isempty([Field1]) then 'Null'
Elseif ([Field1]-Round([Field1], 1))=0 then 'Whole'
Else 'Not Whole' Endif

Check and let me know.

[TomWelgemoed]

Hi @reginawhelan ,

Could you post some data that I can review?

Thanks.

Regards,

Tom

[atcodedog05]

Hi @TomWelgemoed 

We are trying to find Whole(7) and Not Whole(7.2) and i tried mod(n,1) it doesnt work on decimal.

[TomWelgemoed]

Hi @atcodedog05 ,

Right you are, ignore my previous post. Think we need to see the end-to-end workflow.