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

0 Members and 1 Guest are viewing this topic.

#### hidden layer

• Full Member
• Posts: 102
##### 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: 4520
• 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: 102
##### 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: 4520
• 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: 102
##### 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: 4520
• 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