### Author Topic: nested If- statements - result #Value!  (Read 1191 times)

0 Members and 1 Guest are viewing this topic.

#### hidden layer

• Full Member
• Posts: 123
##### nested If- statements - result #Value!
« on: March 19, 2023, 06:35:21 AM »
Hello,

The scenario is as follows: A shape's background color may be toggled from red to blue (and vice versa) by its context menue. This works fine.
The formula in User.toggle is =IF(RED(FillBkgnd)=255,TRUE,FALSE)

The formula in User.time is =IF(User.toggle,MODULUS(SECOND(NOW()),2)=1,"xyz").
So the fomula calculated if the change to red happened in a even or uneven second. This works as well.

The formula in User.time.Prompt is =IF(User.time,"odd",IF(NOT(User.time),"even",IF(User.time="xyz",0,0))).
In case the shape's background turned to red the formula calculate correctly but when it turns to blue the result is #Value!.
Also =IF(User.time,"odd",IF(NOT(User.time),"even",0)) gives the result #Value!.

Why it doesn't calculate 0?

I didn't try to solve this with ISERRVALUE yet. May I?

Googeling "visio shapesheet result error "#Value!" " doesn't give lots of matches, so I'm a bit clueless.
Does someone has an idea (or even better: a solution)?

Thanks and have a nice weekend
hl

#### wapperdude

• Global Moderator
• Hero Member
• Posts: 4837
• Ideas Visio-lized into solutions
##### Re: nested If- statements - result #Value!
« Reply #1 on: March 19, 2023, 04:19:38 PM »
The syntax IF(user.time="xyz"  is invalid.  You need to use strsame(user.time,"xyz")
Visio 2019 Pro

#### hidden layer

• Full Member
• Posts: 123
##### Re: nested If- statements - result #Value!
« Reply #2 on: March 20, 2023, 02:27:01 AM »
Thanks wapperdude!
if searched here for IF STRSAME there are lots of matches issuing the same topic. As said by
Quote
Visio Guy: EVERYONE misses this, so don't worry about it.
I relax a bit

but...
hmm... that's strange...
this is not so obvious in M\$ description (what is not a proof at all):

logicalexpression  Required   String   Expression to evaluate.

But this: =IF(User.time,"odd",IF(NOT(User.time),"even",IF(STRSAME(User.time,"xyz",FALSE),0,0))) also results #Value!.

I tested in another cell =IF(STRSAME(User.time.Prompt,"odd"),1,0) which gives 1 if the previous (in terms of time) result was "odd".
I don't get it. Sorry.

User.odd =IF(STRSAME(User.time,"xyz"),0,User.time)
User.even =IF(STRSAME(User.time,"xyz"),0,NOT(User.time))

So another workaround was found.

CU
hl

#### wapperdude

• Global Moderator
• Hero Member
• Posts: 4837
• Ideas Visio-lized into solutions
##### Re: nested If- statements - result #Value!
« Reply #3 on: March 20, 2023, 10:27:45 AM »
Quote
=IF(User.time,"odd",IF(NOT(User.time),"even",IF(STRSAME(User.time,"xyz",FALSE),0,0)))

Your syntax is wrong.  I'm not @ my computer, but this s/b correct:
=IF(User.time,"odd",IF(NOT(User.time),"even",IF(STRSAME(User.time,"xyz"),0,0)))

The problem is that the if is looking for a Boolean evaluation and User.time = "xyz" is a string comparison.  Hence the error.  The strsame() does that, and returns a true or false Boolean result.  It's not a workaround issue.  The "0,0" are result choices that get selected as a result of the comparison.  In this case, the 1st "0" is result if xyz is true, the 2nd "0" for false.  Note, this last result means that all 3 IFs are, in fact, false.  If that's the case, you could merely use this construct:  =IF(User.time,"odd",IF(NOT(User.time),"even",0)), which gives as possible results odd, even, and 0 for anything else.

HTH
Visio 2019 Pro

#### hidden layer

• Full Member
• Posts: 123
##### Re: nested If- statements - result #Value!
« Reply #4 on: March 20, 2023, 12:46:27 PM »
Hi Wapperdude,
of course this helps!
Thank you for patience but maybe I have another problem because you wrote:
Quote
Your syntax is wrong.  I'm not @ my computer, but this s/b correct:
=IF(User.time,"odd",IF(NOT(User.time),"even",IF(STRSAME(User.time,"xyz"),0,0)))

My formula in post #3 was the same:
Quote
But this: =IF(User.time,"odd",IF(NOT(User.time),"even",IF(STRSAME(User.time,"xyz",FALSE),0,0))) also results #Value!.
.
The only difference is the (optional) FALSE statement in the STRSAME formula - not to ignore...

Also your blue solution is exact the same as I tried in the 1st post.

I know that sometimes it's just a comma or a point what easiely can be overlooked...

So all formulas I've tested - see attachment. Always an error message.
The other thing is that the "display"- shape will not be calculated correctly (imho).

Is there anything wrong with my settings?

#### wapperdude

• Global Moderator
• Hero Member
• Posts: 4837
• Ideas Visio-lized into solutions
##### Re: nested If- statements - result #Value!
« Reply #5 on: March 20, 2023, 03:58:41 PM »
My apologies, that construct does indeed fail.  The problem is the 1st two IFs want a boolean testable argument, so the string value is a problem.  The order must be changed to catch the string xyz 1st.  Thus, the co nstruct looks like this:  =IF(STRSAME(User.time,"xyz"),"xyz",IF(User.time,"odd",IF(NOT(User.time),"even",0)))

« Last Edit: March 20, 2023, 07:48:45 PM by wapperdude »
Visio 2019 Pro

#### vojo

• Hero Member
• Posts: 1710
##### Re: nested If- statements - result #Value!
« Reply #6 on: March 21, 2023, 07:19:33 AM »
you might realize now that its better to use several cells to build a complex formula

user.cond1 = if (<check this>, true, false)
user.cond2 = if (<check that>, true, false)
user.cond3 = if (<this othr thing>, true,if (that crazy thing,true, false))
user.test = or(user.cond1,user.cond2,user.cond3)

easier to debug the complex formula by watch the values of cells

#### hidden layer

• Full Member
• Posts: 123
##### Re: nested If- statements - result #Value!
« Reply #7 on: March 21, 2023, 09:49:44 AM »
Hi,
so an "IF" is not an "IF".... especially if nested -  good to know.

In fact I remember that I had a similar problem years ago in Excel - so that makes sense.

something learned again!

Thanks a lot wapperdude!

I'll update David Parker's "Shapesheet's fomulas A-Z" files accordingly - which is full of notes here and there

@vojo - I thought that the more cells are there the larger the file is and the more time a calculation needs. Ok, my sheets are reacting just in time even with 250 shapes on it.
As I mentioned David Parker - here is a formula in these files:
Code
``=IF(User.Side=1,PNT(Width,IF(PNTY(User.LeaderEnd)<0,Height*0,IF(PNTY(User.LeaderEnd)>Height,Height,IF(AND(PNTX(User.LeaderEnd)<0,PNTY(User.LeaderEnd)<Height*0.5),Height*0,IF(AND(PNTX(User.LeaderEnd)<0,PNTY(User.LeaderEnd)>=Height*0.5),Height,Height*0.5+Width*0.5*(PNTY(User.LeaderEnd)-Height*0.5)/(PNTX(User.LeaderEnd)-Width*0.5)))))),IF(User.Side=2,PNT(IF(PNTX(User.LeaderEnd)<0,Width*0,IF(PNTX(User.LeaderEnd)>Width,Width,IF(AND(PNTY(User.LeaderEnd)<0,PNTX(User.LeaderEnd)<Width*0.5),Width*0,IF(AND(PNTY(User.LeaderEnd)<0,PNTX(User.LeaderEnd)>=Width*0.5),Width,Width*0.5+Height*0.5*(PNTX(User.LeaderEnd)-Width*0.5)/(PNTY(User.LeaderEnd)-Height*0.5))))),Height),IF(User.Side=3,PNT(Width*0,IF(PNTY(User.LeaderEnd)<0,Height*0,IF(PNTY(User.LeaderEnd)>Height,Height,IF(AND(PNTX(User.LeaderEnd)>Width,PNTY(User.LeaderEnd)<Height*0.5),Height*0,IF(AND(PNTX(User.LeaderEnd)>Width,PNTY(User.LeaderEnd)>=Height*0.5),Height,Height*0.5-Width*0.5*(PNTY(User.LeaderEnd)-Height*0.5)/(PNTX(User.LeaderEnd)-Width*0.5)))))),PNT(IF(PNTX(User.LeaderEnd)<0,Width*0,IF(PNTX(User.LeaderEnd)>Width,Width,IF(AND(PNTY(User.LeaderEnd)>Height,PNTX(User.LeaderEnd)<Width*0.5),Width*0,IF(AND(PNTY(User.LeaderEnd)>Height,PNTX(User.LeaderEnd)>=Width*0.5),Width,Width*0.5-Height*0.5*(PNTX(User.LeaderEnd)-Width*0.5)/(PNTY(User.LeaderEnd)-Height*0.5))))),Height*0))))``
.
This is a complex formula. Even if I couldn't do it without Excel I wouldn't at all.
But you're right! for debugging it's easier to check constructions like this.
The "xyz"- thing I had to solve at first anyway I found out. But thanks!

#### wapperdude

• Global Moderator
• Hero Member
• Posts: 4837
• Ideas Visio-lized into solutions
##### Re: nested If- statements - result #Value!
« Reply #8 on: March 21, 2023, 10:30:05 AM »
Quote
so an "IF" is not an "IF".... especially if nested -  good to know.
Incorrect conclusion...IF is always IF, but you cannot violate it's syntax.  It works when nested, but you have to build it properly.
Vojo's point has a 2 additional important aspects:  month, year from now, the nested structure is difficult to recall what it does; smaller IF are easier to edit/embellish.

In the example I previously attached, you van see some of the above aspects...the use of smaller IFs for debugging and also, a fully catenated structure.  But, there is a caveat where this full structure fails. It only tests for a string identical to "xyz".  Any other string would pass thru as a false evaluation and the subsequent IFs will blow up.  Thus, there actually should be a specific test for presence of improper string.  The fault is not the nesting of IFs, but the presence of an invalid value.
« Last Edit: March 22, 2023, 01:23:20 AM by wapperdude »
Visio 2019 Pro

#### vojo

• Hero Member
• Posts: 1710
##### Re: nested If- statements - result #Value!
« Reply #9 on: March 21, 2023, 11:47:01 AM »
I believe max characters in a cell is 127, so I am pretty sure you cant place parker formula in a single cell
debugging parker formula as stated would be very hard to debug.

in my eperience, rarely does anybody use up so many rows that the file size gets unruly

#### hidden layer

• Full Member
• Posts: 123
##### Re: nested If- statements - result #Value!
« Reply #10 on: March 21, 2023, 11:56:33 AM »
now I got it.
Already the 1st IF asks: Is the cell's value / content TRUE ?
- Using this very TRUE signalized the IF-statement only  TRUE or FALSE as a valid answer.
- "xyz" isn't and the 1st IF gives this error.

The problem is the 1st two IFs want a boolean testable argument

I thought that IF generally requires a boolean testable argument but I didn't realized that I required it by using TRUE

finally the penny dropped

I was sooo close to the solution with the workaround I used but I didn't get the general point.

Thanks again!

hl

#### hidden layer

• Full Member
• Posts: 123
##### Re: nested If- statements - result #Value!
« Reply #11 on: March 21, 2023, 12:05:40 PM »
https://bvisual.net/resources/shapesheet-functions-a-z/
The formula is in the User.WHBoxIntersection cell of the white description window almost on top.

If I would do like this than only to obfuscate. Maybe it's nonsense, maybe it should be hard to re-engineer it - who cares?