Floor and Ceiling function help

[MM_Chris]

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.

[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

[MM_Chris]

Hi Kane,

The reason I would want to round 1500 down because the some numbers that I round up are too much than the actual sum I'm supposed to be getting. So in that case, I would have to choose the numbers with least significance to get rounded up. In this case 1552 would have less of a significance than say 1578. I hope that makes sense. Your idea of the multi-stage process seems to be very helpful. I will try that and let you know what I came up with. I also thought about using a ratio from the actual sum to the numbers provided.

Thank you!

[mceleavey · Accepted solution]

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.

M.

[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

[MM_Chris]

Thank you again @mceleavey !

Chris

[Matt_Sinker]

Hello there,

Please can anyone help on the following, new to Alteryx and trying to include the following formula but having no luck.

The data is from an SQL Query, the first formula I have included is an average of x3 of the columns in a new column named "Avg Weekly Fcast Units", appears to have worked fine, however trying to add the next column which needs to be "Peak Daily Forecast Units". This calc in excel would be:

=CEILING((Avg Weekly Fcast Units*0.23),1)

As far as I can tell the formula needs to read: 

CEIL(([Avg Weekly Fcast Units]*0.23)/1)

But i am returning an error saying: Formula: tried to apply numeric operator to a string value. I am assuming this error refers to the fact I am referencing a column name and trying to apply a numerical equation, is there a way around this?

[mceleavey]

Hi @Matt_Sinker,

drop a select tool before the formula tool in which you are building this, this will tell you the data type of the [Avg Weekly Fcast Units] field. If you are creating this field in the formula tool itself, ensure the data type on the formula is set to be a numeric type.

If the select tool shows it as something like a string, you can simply change it to a numeric type (eg. double).

You can also add the tonumber feature to your formula:

CEIL((tonumber([Avg Weekly Fcast Units])*0.23)/1)

Hope this helps.

M.

[Matt_Sinker]

Perfect! Thanks for your help, that worked perfectly!

Many Thanks.