Floor and Ceiling function help

Hi there,

I was wondering if there was a way to parse through data and see which values need to be rounded either up or down. I know we have the floor or ceiling formulas, but would REGEX be useful for this case to check the decimal value to make sure whether that value needs to be rounded up or down?

Thank you.

Tips and Tricks

Preparation

Accepted answers

mceleavey

Hi @MM_Chris,

I've attached a simple workflow to carry out this function. There isn't really a native parameter driven FLOOR function, so I simply divided the number by 500,000, split the result on the decimal point, and took the figure before the decimal and multiplied it by 500,000 to give the multiples of that.

I've attached the workflow.

Let me know if this does what you want.

Solution for MM_Chris.yxmd

All comments

MM_Chris

To provide a better example if I have a number 1552 I would want to round that down to 1552 I would want to round that down to 1500 instead of 1600.

Thank you again.

- Chris

KaneG

Entirely depends on your rounding rules. Why would 1552 be rounded down to 1500? If it was rounded up, then ROUND([X]/100,1)*100 would work.

In order to use REGEX, you will have to deal with numbers as text and vice versa, but you should be able to do it.

Without testing much, the following REGEX should split it into the front part and the last 2 digits, so then you can test parts and adjust.

(\d{1,5})(\d\d)

However, I would probably approach it in a multi-stage process and then try to stitch them together. For instance, split into separate fields > test > adjust > stitch back together.

Kane

mceleavey

Hi @MM_Chris,

I've attached the workflow.

Let me know if this does what you want.

Solution for MM_Chris.yxmd

MM_Chris

Hi @mceleavey,

Yes this helped. Another community member helped me with this a while back and I think the methodology is the same. Instead of using Formula -> Text to Columns -> Formula, I just use one Formula tool with the expression: FLOOR([Number]/100) * 100

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703