nested If- statements - result #Value!

Started by hidden layer, March 19, 2023, 11:35:21 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

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")
Visio 2019 Pro

hidden layer

Thanks wapperdude!
if searched here for IF STRSAME there are lots of matches issuing the same topic. As said by
QuoteVisio 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

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

Hi Wapperdude,
of course this helps!
Thank you for patience but maybe I have another problem because you wrote:
QuoteYour 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:
QuoteBut 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

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

Visio 2019 Pro

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:
=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

#8
Quoteso 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.
Visio 2019 Pro

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:
Quote from: wapperdude on March 20, 2023, 08:58:41 PM
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?