Formula help

[htalteryx]

Can you please help me with a formula to get the result on the right?  I tried an IF CONTAIN formula, but it's not working.

110000.6

110000.3

[htalteryx]

I have a lot of values containing .6 and want to change them to .3

[CharlieS]

There's a little converting that you'll need to do for those string functions to work on that numeric value. Try this:

tonumber(replace(tostring([Field1]),".6",".3"))

Formula Breakdown:

1) You're going to replacing the string ".6" with ".3", but this has to be performed on a string value. Convert the value ([Field1]) using "tostring([Field1])".

2) Then perform the replacement using "replace(tostring([Field1]),".6",".3")"

3) Once you have that replacement done, convert the final result back into a numeric value using "tonumber("

[htalteryx]

Can you provide a workflow?

[htalteryx]

I got it to work, but I have a new problem.  How do I add two or more conditions so that any value containing .6 is updated to .3, .61 is updated to .31, and any value with no decimal is updated to .2?

[cmcclellan]

I wouldn't convert to string, I'd just do this:

Code is:

if 
round([Field1] -  floor([Field1]),.1) = 0.6
then [Field1] - 0.3
else [Field1]
endif

[CharlieS]

I've attached a workflow that shows off a a few tools that can be used to achieve this. Let me know if you have any questions.

[cmcclellan]

Can you test this please ?

if 
round([Field1] -  floor([Field1]),.2) = 0.61 or round([Field1] -  floor([Field1]),.1) = 0.6
then [Field1] - 0.3
elseif floor([Field1]) = [Field1] then [Field1] + 0.2
else [Field1]
endif

[htalteryx]

I got a red error "Type mismatch in operator +".  Do you know how to fix?

[cmcclellan]

It's working for me ....