Problem rounding the way Excel does

hiva

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!

seven

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.

community_answer_excel_rounddown.yxmd