### Author Topic: CEILING function broken?  (Read 1684 times)

0 Members and 1 Guest are viewing this topic.

#### zhuravsky

• Jr. Member
• Posts: 59
##### Re: CEILING function broken?
« Reply #15 on: September 19, 2023, 12:38:34 AM »
Hmm... Looks like I was too optimistic...
I do not know why, but there is a problem with EVALCELL function.

Width of the shape is 20 mm,
ThePage!XGridSpacing=2 mm
User.RSEILING=INT(10*CEILING(ARG("A"),MAX(ThePage!XGridSpacing,2 mm)))/10

EVALCELL(User.RCEILING,"A",10 mm) = 10 mm
EVALCELL(User.RCEILING,"A",Width*0.5) = 12 mm

And where I'm wrong?

#### gtfox

• Newbie
• Posts: 4
##### Re: CEILING function broken?
« Reply #16 on: September 19, 2023, 01:45:10 AM »
EVALCELL(User.RCEILING,"A",10 mm) = 10 mm
EVALCELL(User.RCEILING,"A",Width*0.5) = 12 mm
maybe Width*0.5*1mm
« Last Edit: September 19, 2023, 06:18:52 AM by gtfox »

#### zhuravsky

• Jr. Member
• Posts: 59
##### Re: CEILING function broken?
« Reply #17 on: September 19, 2023, 06:55:57 AM »
Strange, after Visio restart I can not repeat initial bugs, looks like now calculation is ok.

#### wapperdude

• Global Moderator
• Hero Member
• Posts: 4834
• Ideas Visio-lized into solutions
##### Re: CEILING function broken?
« Reply #18 on: September 19, 2023, 05:39:36 PM »
I'm attaching a test file because it seems your formula does not evaluate correctly for various width values.  20 mm seems to be a special case.  The ceiling function should round up to the nearest integer that is determined by the 2nd value.  Your formula doesn't adhere to this rule.  If your formula is correct, then, perhaps a less obnoxious fcn other than ceiling would be better suited???

So, I looked at simplifying the ceiling function as provided.  Ran into a really weird case.  It doesn't catch the value correctly (at least in metric), if it is parameterized.  To wit, your Arg("A") = width*0.5.  The value in the cell looks fine, 20 mm * 0.5 = 10 mm, but the Ceiling fcn doesn't evaluate it correctly.  I had to spoof it.  That is, width*0.5 * 1 mm/1mm.

Not a big fan of ceiling fcn.  See attached.  Try different values for width.

Visio 2019 Pro

#### wapperdude

• Global Moderator
• Hero Member
• Posts: 4834
• Ideas Visio-lized into solutions
##### Re: CEILING function broken?
« Reply #19 on: September 19, 2023, 06:00:02 PM »
Fixed the spoofing problem using Format() fcn.

See updated attachment.
Visio 2019 Pro

#### wapperdude

• Global Moderator
• Hero Member
• Posts: 4834
• Ideas Visio-lized into solutions
##### Re: CEILING function broken?
« Reply #20 on: September 19, 2023, 06:19:42 PM »
Founnd solution for the "scalar" issue...Formatex() fcn.  Turns out if the source and destination units are identical, it drops the units.  Whoda thought?

Updated attachment.

« Last Edit: September 19, 2023, 06:46:47 PM by wapperdude »
Visio 2019 Pro

#### gtfox

• Newbie
• Posts: 4
##### Re: CEILING function broken?
« Reply #21 on: September 20, 2023, 10:33:01 AM »
CeilingTest3.vsdx
seems to work well. Visio 2013
« Last Edit: September 20, 2023, 10:35:39 AM by gtfox »

#### wapperdude

• Global Moderator
• Hero Member
• Posts: 4834
• Ideas Visio-lized into solutions
##### Re: CEILING function broken?
« Reply #22 on: September 20, 2023, 12:08:29 PM »
Is the horse dead???  I seem to keep flogging it.  Yes.  Another update.

Since the Ceiling fcn seems to be working correctly, and the goal is to have the shape increment in steps of page grid spacing, then, a further simplification is possible and along with relocating the formula, using SETATREF fcns, to the width cell.  I set minimum width = 4 mm.  Within the shapesheet, in the User Section, Prompt cell, there is, for convenience, a method to change the grid spacing.  Note, vert grid = horiz grid.  Also, for my convenience, shape height = shape width.

As user changes the size of the shape, it will only increment to multiples of the page grid.

Enjoy!
Visio 2019 Pro

#### gtfox

• Newbie
• Posts: 4
##### Re: CEILING function broken?
« Reply #23 on: September 21, 2023, 02:58:54 PM »
CeilingTest4.vsdx
Enjoy!
looks good, great job