News:

BB code in posts seems to be working again!
I haven't turned on every single tag, so please let me know if there are any that are used/needed but not activated.

Main Menu

CEILING function broken?

Started by Nikolay, September 16, 2023, 10:54:18 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

zhuravsky

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

#16
Quote from: zhuravsky on September 19, 2023, 05:38:34 AMEVALCELL(User.RCEILING,"A",10 mm) = 10 mm
EVALCELL(User.RCEILING,"A",Width*0.5) = 12 mm
maybe Width*0.5*1mm

zhuravsky

Strange, after Visio restart I can not repeat initial bugs, looks like now calculation is ok.

wapperdude

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

Fixed the spoofing problem using Format() fcn.

See updated attachment.
Visio 2019 Pro

wapperdude

#20
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.

Visio 2019 Pro

gtfox

#21
Quote from: wapperdude on September 19, 2023, 11:19:42 PMCeilingTest3.vsdx
seems to work well. Visio 2013

wapperdude

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


Browser ID: smf (possibly_robot)
Templates: 4: index (default), Display (default), GenericControls (default), GenericControls (default).
Sub templates: 6: init, html_above, body_above, main, body_below, html_below.
Language files: 4: index+Modifications.english (default), Post.english (default), Editor.english (default), Drafts.english (default).
Style sheets: 4: index.css, attachments.css, jquery.sceditor.css, responsive.css.
Hooks called: 283 (show)
Files included: 34 - 1306KB. (show)
Memory used: 1129KB.
Tokens: post-login.
Cache hits: 13: 0.00261s for 26,732 bytes (show)
Cache misses: 2: (show)
Queries used: 15.

[Show Queries]