Hello!
In excel, the values in column B are calculated with this formula: =ROUNDDOWN(A*-0.029,2)
In Alteryx I'm using Round(([A]*-0.029),0.01)
Please help me get the exact same numbers that Excel returns.
A | B - Excel | B - Alteryx |
6982.04 | -202.47 | -202.48 |
5907.88 | -171.32 | -171.33 |
Thanks!
Solved! Go to Solution.
The equivalent would be:
INT(100*[A]*-0.029)/100
RoundDown always moves towards 0
INT will do the same in these case. By multiplying/dividing by 100 you get the first 2 decimal places.
Hope it helps
I don't think I can find INT() in Alteryx.
Just to make myself more clear, I want to return in Alteryx the values that excel returns.
The prior answer references an unknown function.
Here's the solution:
FLOOR(ABS([A] * -2.9)) / 100 * (([A] / ABS([A])) * -1)
Let's walk through an example.
Let [A] = 6982.04
Step 1, multiply the field by 100 and by -0.029.
= [A] * 100 * -0.029
= [A] * -2.9
= 6982.04 * 100 * -0.029
= 6982.04 * -2.9
Step 2,take the absolute value of the result from step 1.
= ABS([step1])
= ABS([A] * -2.9)
= ABS(6982.04 * -2.9)
= ABS(-20247.916)
= 20247.916
Step 3, take the floor of the result of step 2 and divide by 100. We take the floor to return the largest integer less than or equal to our number. We divide by 100 to "undo" the multiplication of 100 in step 1.
= FLOOR([step2]) / 100
= FLOOR(ABS([A] * -2.9)) / 100
= FLOOR(20247.916) / 100
= 20247 / 100
= 202.47
Step 4, we must "retrieve" the correct sign (+/-) which we lost due to the absolute value function.
Because [A] divided by the absolute value of itself gives us the sign of the original value and we multiplied it by -0.029, we have:
= [step3] * (([A] / ABS([A])) * -1)
= 202.47 * ((6982.04 / ABS(6982.04)) * -1)
= 202.47 * 1 * -1
= -202.47
That's what we wanted. With Alteryx, there is typically more than one solution. I'll show another without the deep dive above.
IF [A] < 0
THEN CEIL([A] * -2.9) / 100
ELSE FLOOR([A] * -2.9) / 100
ENDIF
Note that my first solution will produce divide by zero warnings when [A] = 0 while the second solution will not produce the warning and will return 0.
I have attached a sample workflow with the walkthrough and results.
Perfect, option 1 works!
Option 2 doesn't seem to work, but it's fine because I won't have 0's in my data.
Thanks.