nested If- statements - result #Value!

[hidden layer]

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]

The syntax IF(user.time="xyz"  is invalid.  You need to use strsame(user.time,"xyz")

[hidden layer]

Thanks wapperdude!
 if searched here for IF STRSAME there are lots of matches issuing the same topic. As said by

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.

This works instead:
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]

=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

[hidden layer]

Hi Wapperdude,
of course this helps!
Thank you for patience but maybe I have another problem because you wrote:

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:

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]

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)))