CEILING function broken?

[Nikolay]

What should be the result of "CEILING(6cm, 2cm)"?

https://learn.microsoft.com/de-de/office/client-developer/visio/ceiling-function

Visio 2003 (yes, 2003):
CEILING(6cm, 2cm) => 6.0000cm

Visio 2007, 2010, 2021, Plan2
CEILING(6cm, 2cm) => 6.2992cm

WHY?

[wapperdude]

V2019 Pro => same result.  But....
Ceiling(6 in, 2 in) => 6.0000 in
Ceiling(6, 2) => 6.0000
Ceiling(6 cm) => 6.0000 cm
Ceiling(6 cm, 2) => 6.0000 cm

[wapperdude]

Tried a couple other metric cases.  Gives the correct answer as long as multiple number doesn't have units specfied.

Would seem there is a bug, though.  The case:  Ceiling(X cm, A in) also gives correct result.  Note, the function works on number values and it would seem that units are not handled properly. 

[zhuravsky]

Any way to convert mm into non-dimensional value? (2.5 mm -> 2.5)?

[wapperdude]

Not sure what you mean?  Do you mean as applied to the ceiling & floor fcns?  Technically, the method is to  just divide by 1 mm.  Practically, just drop the units.  However, Visio correctly handles the units for the 1st number, the one being processed.  Just omit the units from the 2nd (multiplicity) value.

Hope I understood you correctly.

[Nikolay]

In the practical case, the second argument is coming from another cell, of course. How to remove units in this case?

User.X1 = CEILING(6cm, Width)
Width = 2cm

I can't believe the bug is here for the past 20 years and nobody has noticed?

[Surrogate]

How to remove units in this case?

User.X1 = CEILING(6cm, Width)

Something like

Code Select Expand

User.X1 = CEILING(6cm, Width/1 cm)

I can't believe the bug is here for the past 20 years and nobody has noticed?

To slightly paraphrase your comment in the other thread:

Just wondering if anybody REALLY uses these ribbon shortcuts?

this CEILING function?
Are you sure that billions of users have been actively using this function all these years? 

[Nikolay]

I mean, this should have popped up in some shapes. That they behave oddly when in metric diagrams.

[JohnGoldsmith]

Hi Nikolay and all,
It looks like the second argument is converted to inches prior to consumption by the ceiling calculation.  So:

Code Select Expand

CEILING(6 cm, 2cm)
is equivilent to

Code Select Expand

CEILING(6 cm,0.7874) // which evaluates to 6.2992

I'm not sure on why this is differnt between 2003 and now though.
If you can't be sure what units you'll be referencing in multiple argument then maybe the FORMAT function might work here.  For example:
CEILING(6 cm,FORMAT(2 cm,"0.####")) // where 2cm might be a refernce to another cell
Normally the FORMAT function only changes the displayed value but I think, in the context of the outer CEILING function, it works to evaluate as a number rather than a unit number.
All the best
John

[Nikolay]

Yes, I understand the possible reason for the bug is the unit conversion.
But the meaning of this function is to round the first value by the multiple of the second. Why the hell does it convert it to inches?

That is, unit conversion IS the bug here, IMHO.

Doesn't the 2003 behavior look logical??

As for me, I don't need the practical solution actually; this is just to highlight the problem.
I've probably never used this function (especially with two arguments), i.e. my interest here is purely theoretical:)

[Paul Herber]

my interest here is purely theoretical

Ah, a troublemaker! 

[wapperdude]

Add a couple more 😂😂 to Paul's comment.  And I thought we were in search of an achievable solution!  🤔😅😅 😉

[wapperdude]

Bottom line... there is no SCALAR value function is shapesheet that fetches value sans units.

[zhuravsky]

"Ah, a troublemaker!"

Actually, Nicolay is not guilty. I found this issue and have opened the thread here:
https://visio.getbb.ru/viewtopic.php?f=5&t=1960
My solution for now is following:
User.RCEILING = INT(10*CEILING(ARG("A"),MAX(ThePage!XGridSpacing,2 mm)))/10
ConnectionPoint.X1 = EVALCELL(User.RCEILING,"A",Width*0.5)
Looks a little bit complicated but works for me.
I needed to allign my connection points to the grid of page when user changes width of the shape.

[wapperdude]

Well that takes the fun out of it...letting Nikolay off the proverbial hook. 

Glad you have solution.