Author Topic: shapesheet formula incorrect, calculating wrong result.  (Read 4021 times)

0 Members and 1 Guest are viewing this topic.

abssorb

  • Jr. Member
  • **
  • Posts: 42
shapesheet formula incorrect, calculating wrong result.
« on: May 12, 2011, 07:05:43 AM »
Shapesheet is giving errors in calculation.

Wapperdude has been helping me with a requirement:
http://visguy.com/vgforum/index.php?topic=2736.0

I have a value calculated from two dates, three years apart. This represents a line, and the value is 1094.5030 ed.
The width of the line in cm is 36.0547.

To find the no of days per cm, 1094.5030 is divided by 36.0547.  The answer in the shapesheet is 77.1061.
It should be 30.356, and so is out by a factor of 2.54

In Wapperdude's original, the exact same formulae give the correct results.  The only difference is the original is in inches, and my page is in in cm.  Which matches 2.54.

Any thoughts where I can fix this? (Visio 2003)
« Last Edit: May 12, 2011, 07:12:05 AM by abssorb »

aledlund

  • Hero Member
  • *****
  • Posts: 1412
Re: shapesheet formula incorrect, calculating wrong result.
« Reply #1 on: May 12, 2011, 07:08:51 AM »
visio uses inches as the default measurement under the covers, not centimeters.
al

abssorb

  • Jr. Member
  • **
  • Posts: 42
Re: shapesheet formula incorrect, calculating wrong result.
« Reply #2 on: May 12, 2011, 08:05:17 AM »
So when I switch the shapesheet view from Formulas to Values,  there's an unseen calculation taking place and the value shown to me is not actually the value?

The real value is 14.19476378.  The shapeheet Values view shows me not the real value, but 36.0547, and doesn't tell me anywhere that it's done this.

OK. Switch my mind to Imperial. no problem.

Thanks, that's helpful - I've made a note. For others facing the same problem, it necessary to multiply / divide such values by 2.54 :)


Yacine

  • Hero Member
  • *****
  • Posts: 2703
Re: shapesheet formula incorrect, calculating wrong result.
« Reply #3 on: May 12, 2011, 01:09:27 PM »
if you calculate explicitely with cm, you should get the right result.
Yacine

abssorb

  • Jr. Member
  • **
  • Posts: 42
Re: shapesheet formula incorrect, calculating wrong result.
« Reply #4 on: May 13, 2011, 03:13:02 AM »
if you calculate explicitely with cm, you should get the right result.

That's what I'd expect, and how I assumed it worked.  I.e. the base units could be inches or parsecs - it shouldn't matter, in the same way that excel never applies its own interpretations of a value.

I've looked for global settings somewhere but can't find anything.

Visio Guy

  • Administrator
  • Hero Member
  • *****
  • Posts: 1716
  • Smart Graphics for Visual People...n' Stuff
    • Visio Guy
Re: shapesheet formula incorrect, calculating wrong result.
« Reply #5 on: May 13, 2011, 04:19:50 AM »
When you do a calculation like Width/User.SomeDuration, you are mixing units: length and elapsed time. From what I can see, Visio is trying to make things as basic as possible, and therefore reverts to inches (IU - Internal Units) for the length portion of your calculation.

The length units you use don't matter. The result is inches-per-elapsed day. Convert to metric from there.

Here's an example that I typed into two User cells:

4 in/5 ed= 0.8000
2.54 * 4 cm/5 ed= 0.8000

Since the answer is unit-less, you have to make some assumptions about what Visio is doing. Visio can't support a unit of "cm/ed" for example. It only supports linear, duration and areas (ie: m^2 or in^2). I'm not totally sure what Visio is doing with the elapsed-time units, though.

You can do a bit of unit-casting that might make things easier. Here I add 0 cm at the beginning of the expression, and get a result in cm, but should ideally be cm/ed:

0cm + 2.54 * 4 cm/5 ed= 2.0320 cm


For articles, tips and free content, see the Visio Guy Website at http://www.visguy.com
Get my Visio Book! Using Microsoft Visio 2010