Author Topic: How Many IF Functions Can Be Nested in One ShapeSheet Formula?  (Read 15487 times)

0 Members and 1 Guest are viewing this topic.

Jumpy

  • Hero Member
  • *****
  • Posts: 1061
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
« Last Edit: June 30, 2010, 04:22:34 AM by Visio Guy »

Trex2001

  • Newbie
  • *
  • Posts: 2
Re: How Many IF Functions Can Be Nested in One ShapeSheet Formula?
« Reply #1 on: June 29, 2010, 07:50:58 AM »
Although it doesn't answer your question directly, maybe this link helps.

Couldn't find anything else on limitations, though.

 8) First post  ;D
« Last Edit: June 30, 2010, 04:23:03 AM by Visio Guy »

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4841
  • Ideas Visio-lized into solutions
Re: How Many IF Functions Can Be Nested in One ShapeSheet Formula?
« Reply #2 on: June 29, 2010, 09:07:38 AM »
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.

« Last Edit: June 30, 2010, 04:22:55 AM by Visio Guy »
Visio 2019 Pro

Jumpy

  • Hero Member
  • *****
  • Posts: 1061
Re: How Many IF Functions Can Be Nested in One ShapeSheet Formula?
« Reply #3 on: June 29, 2010, 03:30:08 PM »
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
« Last Edit: June 30, 2010, 04:22:50 AM by Visio Guy »

Visio Guy

  • Administrator
  • Hero Member
  • *****
  • Posts: 1737
  • Smart Graphics for Visual People...n' Stuff
    • Visio Guy
Re: How Many IF Functions Can Be Nested in One ShapeSheet Formula?
« Reply #4 on: June 30, 2010, 04:21:30 AM »
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.
« Last Edit: June 30, 2010, 04:45:14 AM by Visio Guy »
For articles, tips and free content, see the Visio Guy Website at http://www.visguy.com
Get my Visio Book! Using Microsoft Visio 2010

Jumpy

  • Hero Member
  • *****
  • Posts: 1061
Re: How Many IF Functions Can Be Nested in One ShapeSheet Formula?
« Reply #5 on: June 30, 2010, 04:49:40 AM »
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

Jumpy

  • Hero Member
  • *****
  • Posts: 1061
Re: How Many IF Functions Can Be Nested in One ShapeSheet Formula?
« Reply #6 on: June 30, 2010, 04:55:13 AM »
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

Visio Guy

  • Administrator
  • Hero Member
  • *****
  • Posts: 1737
  • Smart Graphics for Visual People...n' Stuff
    • Visio Guy
Re: How Many IF Functions Can Be Nested in One ShapeSheet Formula?
« Reply #7 on: June 30, 2010, 05:04:08 AM »
Interesting about the character-count, Jumpy!

I had thought that a cell's formula-length 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 formula-length problems.
For articles, tips and free content, see the Visio Guy Website at http://www.visguy.com
Get my Visio Book! Using Microsoft Visio 2010

Visio Guy

  • Administrator
  • Hero Member
  • *****
  • Posts: 1737
  • Smart Graphics for Visual People...n' Stuff
    • Visio Guy
For articles, tips and free content, see the Visio Guy Website at http://www.visguy.com
Get my Visio Book! Using Microsoft Visio 2010