Visio Guy
Visio => ShapeSheet & Smart Shapes => Topic started by: Jumpy on June 29, 2010, 06:26:10 AM

Hello,
does anyone know, how many "If" in one formula are allowed?
For example in Excel you can use at max only 7 "If", if I remember correctly.
I'm buliding a mega conditional formula (at the moment only on paper, but I want to implement it in someday Visio, too) and if there are limits to the number of "Ifs" I'll have to think about a workarround early.
Thanks for your expiriences,
Jumpy

Although it doesn't answer your question directly, maybe this link (http://www.eggheadcafe.com/software/aspnet/31071632/shapesheetformulalimits.aspx) helps.
Couldn't find anything else on limitations, though.
8) First post ;D

Hmmmm. Don't think I've gone as far as 7...
If there is a limit, you might try breaking it down into smaller pieces, and then chaining the smaller pieces together. Might work.
???
Wapperdude
Did a quick test. Here's a cascade of 10 "IF's". Either open shape sheet and change "User.myVal" or open the shape data and enter a value there. Forgot to implement the double click.

Thanks wapperdude,
looks promising, that my formula could work someday.
@Trex: Thanks, too. And to answer the question in the linked post. I read somewhere, that Visio accepts 64 KByte Data in one cell. That could be used for a rather long formula. My guess is that the probability for an User error (. instead of ! for example, or a missing bracket) increases exponential with the length of the formula and therefore the problems with long formulas.
Good night,
Jumpy

I just got 50 nested IFs to work.
Here's the formula, for the masochists out there:
User.StateName = IF(Prop.StateNumber=1,"Delaware",
IF(Prop.StateNumber=2,"Pennsylvania",
IF(Prop.StateNumber=3,"New Jersey",
IF(Prop.StateNumber=4,"Georgia",
IF(Prop.StateNumber=5,"Connecticut",
IF(Prop.StateNumber=6,"Massachusetts",
IF(Prop.StateNumber=7,"Maryland",
IF(Prop.StateNumber=8,"South Carolina",
IF(Prop.StateNumber=9,"New Hampshire",
IF(Prop.StateNumber=10,"Virginia",
IF(Prop.StateNumber=11,"New York",
IF(Prop.StateNumber=12,"North Carolina",
IF(Prop.StateNumber=13,"Rhode Island",
IF(Prop.StateNumber=14,"Vermont",
IF(Prop.StateNumber=15,"Kentucky",
IF(Prop.StateNumber=16,"Tennessee",
IF(Prop.StateNumber=17,"Ohio",
IF(Prop.StateNumber=18,"Louisiana",
IF(Prop.StateNumber=19,"Indiana",
IF(Prop.StateNumber=20,"Mississippi",
IF(Prop.StateNumber=21,"Illinois",
IF(Prop.StateNumber=22,"Alabama",
IF(Prop.StateNumber=23,"Maine",
IF(Prop.StateNumber=24,"Missouri",
IF(Prop.StateNumber=25,"Arkansas",
IF(Prop.StateNumber=26,"Michigan",
IF(Prop.StateNumber=27,"Florida",
IF(Prop.StateNumber=28,"Texas",
IF(Prop.StateNumber=29,"Iowa",
IF(Prop.StateNumber=30,"Wisconsin",
IF(Prop.StateNumber=31,"California",
IF(Prop.StateNumber=32,"Minnesota",
IF(Prop.StateNumber=33,"Oregon",
IF(Prop.StateNumber=34,"Kansas",
IF(Prop.StateNumber=35,"West Virginia",
IF(Prop.StateNumber=36,"Nevada",
IF(Prop.StateNumber=37,"Nebraska",
IF(Prop.StateNumber=38,"Colorado",
IF(Prop.StateNumber=39,"North Dakota",
IF(Prop.StateNumber=40,"South Dakota",
IF(Prop.StateNumber=41,"Montana",
IF(Prop.StateNumber=42,"Washington",
IF(Prop.StateNumber=43,"Idaho",
IF(Prop.StateNumber=44,"Wyoming",
IF(Prop.StateNumber=45,"Utah",
IF(Prop.StateNumber=46,"Oklahoma",
IF(Prop.StateNumber=47,"New Mexico",
IF(Prop.StateNumber=48,"Arizona",
IF(Prop.StateNumber=49,"Alaska",
IF(Prop.StateNumber=50,"Hawaii",FALSE)
)))))))))))))))))))))))))))))))))))))))))))))))))
Of course I used VBA to generate and stuff the formula. I'll probably publish the file as an article, as soon as I get a chance.

Ok 50 may just be enough for me ;D
Thanks, Chris.
By the way: Word counted the characters in your formula for me and says arround 1276.
Jumpy

An afterthought to your planed article:
Why don't you write it about known limits in Visio. There may be interesting items, for example:
 Number of Prop. Rows (either: that are generally possible or: that can be displayed in Dialog or ShapeData Window)
 Number of User. Rows
 Number of Geometry Sections
 Maximum lenght of a formula
 ...
Jumpy

Interesting about the charactercount, Jumpy!
I had thought that a cell's formulalength was either "limited only by the virtual storage on your PC" or 64000KB, but I don't know for sure.
Recently, I had problems stuffing a very long formula into a cell. My solution was to renamed a referenced cell from "Prop.SomeBigOldName" to "Prop.S".
Since that Prop cell was referenced many, many times, changing the name really reduced the length of the formula.
Perhaps a useful hack if folks run into formulalength problems.

More on this topic:
Stress Test: Nesting “IF” Functions in the ShapeSheet (http://www.visguy.com/2010/07/10/nestingvisioshapesheetiffunctions/)