Hi, I need help with a formula. I need to identify any cells that do NOT have a whole number. Any suggestions?
Solved! Go to Solution.
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 😀
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?
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 😀👍
😞 still not working. Could it be because there is a previous formula calculating this number?
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.
We are trying to find Whole(7) and Not Whole(7.2) and i tried mod(n,1) it doesnt work on decimal.
Hi @atcodedog05 ,
Right you are, ignore my previous post. Think we need to see the end-to-end workflow.