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

[vojo]

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]

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]

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.

[vojo]

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]

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 reason was that I misinterpreted your answer:
 

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]

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?