If Statements in Worksheets

[James Russell]

Hi all,

I've struck a problem jumping between 2010 and 2013, just want to see if someone can replicate it.

I've got a worksheet listing several Lighting Devices with unit numbers from 1 to 200 in this example. So the database search criteria is =DATABASE((R IN ['Lighting Device'])) meaning all lights are picked up.

So there's 200 line items now and I'm putting a formula into cell A1 which will define the whole column's formula.

=('Lighting Device'.'Unit Number')

This gives me the numbers 1 through 200 as I expect.

Now I do the same in B1.

I've chosen an if statement which I thought used to work:

=IF(('Lighting Device'.'Unit Number')>50, 'Greater than 50', 'Less than 50')

But alas all the results returned are 'Greater than 50', when I would expect the first 50 in this example to say 'Less than 50'.

Other operations that I still have in templates like ROUND() and VALUE() are working yet IF doesn't seem to.

Can someone replicate/investigate/solve?

Cheers,

James

[James Russell]

I've solved my own problem to a degree but it raises another issue.

The field for 'Unit Number' as defined by the Spotlight Document settings>Lighting Device properties is a Text field. By inserting the VALUE() command around ('Lighting Device'.'Unit Number) the IF statement returns a correct result. Unfortunately this field is locked and cannot be changed without editing the xml file... (I'm working on it right now )

On another column over though I want to test an ISBLANK equivalent on my positions. About half of my lamps have a position, half have nothing in the field.

Following the help example for IF statements: if (logical_test, value_if_true, value_if_false)

I put in the formula =IF('Lighting Device'.'Position')='', 'BLANK', 'NOT BLANK')

To my surprise the formula disappears and is replaced with =('Lighting Device'.'Position')='' to which all fields reply with a true or false not my text as requested.

Ideas...?

Edited June 3, 2013 by James Russell

[James Russell]

Even though I feel like I'm having a conversation with myself (not unlike usual!) I've made a few interesting discoveries with the new Lighting Device that others might be interested in.

Once a custom Lighting Device has been created it generates an xml file located within the user library; User>Library>Application Support>Vectorworks>2013>Plug-ins>Data.

This file is the preferences file containing data for the field types and misc variables for the Lighting Device plugin, replacing the old Lighting Device.vso. Within each of the fields you'll find a single variable. In my case I've found pesky things like Unit Number as Text (kfieldText) and replaced it with and Int (kfieldLongInt) in my case. This still appears to be a locked value by the over GUI component of the Lighting Device but seems to operate as normal at this stage.

Unfortunately I still haven't found a way to add Pop-up list options to the Lighting Device and although I can create them through the xml file I can't seem to add listing options at this stage.

Obviously if you're going to text this along with me backing up files and using at your own risk is important here. I'll let you all know how it goes!

Still working on the post above, let me know if you have ideas!

[Pat Stanford]

I put in the formula =IF('Lighting Device'.'Position')='', 'BLANK', 'NOT BLANK')

To my surprise the formula disappears and is replaced with =('Lighting Device'.'Position')='' to which all fields reply with a true or false not my text as requested.

Ideas...?

In the quote above you are missing a right Parenthesis. That might be part of the problem. Also I have found that Vectorscript and Worksheet functions seem to like an extra level a parens. Try:

=IF((('Lighting Device'.'Position')=''), 'BLANK', 'NOT BLANK')

[James Russell]

Thanks Pat. I'm always missing parenthhesis all over the place. So that formula now works!

Just so you know the background too all these is that I'm bringing a whole heaps of templates forwards to 2013. Last step, promise.

I've now got a formula from my old template:

=IF((('Lighting Device'.'Position')=''), 0, ROUND(VALUE(('Lighting Device'.'Position'))-0.499))

In this situation the 'position' can only be entered as a number and the worksheet rounds it down to the nearest whole number, a formula I believe you helped me with years ago Pat. Working my way up to here I find that;

=IF((('Lighting Device'.'Position')=''), 0, ROUND(VALUE(('Lighting Device'.'Position'))-0.499))

Works with rounding but returns #Value errors for blank cells.

=IF((('Lighting Device'.'Position')=''), 0, ('Lighting Device'.'Position'))

Works but shows decimals places.

=IF((('Lighting Device'.'Position')=''), 0, VALUE(('Lighting Device'.'Position')))

#Value errors on blank cells, unrounded numbers on filled cells.

=Any combination with ROUND() without VALUE()

Fails with odd numbers presumably because 'Position' is a Text field.

If someone gets a chance to check this over it'd be quite grateful, I'm just going in circles at the moment.

Cheers,

J

[James Russell]

I've attached an example file.

[michaelk]

That's a cool idea.

Are you using this to manage paperwork with lamp bars on trusses?

Does this formula get you what you need?

=(VALUE(IF('Lighting Device'.'Position'='', '0', 'Lighting Device'.'Position'))) DIV 1

I think what's happening is that it is calculating from the inside out. So it looks at the VALUE of the position field first - before the logical test is applied - and that doesn't work if the field is blank. I recall a division by 0 problem like this from a while back.

So I moved the VALUE outside the IF/Then/Else and that seemed to make it happy.

I think DIV 1 yields the same value as Pat's RND trick.

hth

mk

[James Russell]

Michael,

I had come to a similar solution last night but hadn't thought of moving the Value out, that's great!

It's used in paperwork for two scenarios, one as you suggested in the theatrical world for subsidiary items such as bar extension on truss and counter-leavered arms. Secondly in the commercial exhibition world with custom created track layouts. I wrote a script a while ago that creates single and three circuit track as a lighting device from double-line/polygons which is great but there are some layouts such as figure-8 patterns that cannot be created from one loop of track, therefor the breakdown of 1.1, 1.2, 1.11, etc. This way the items of track can be identified independently but counted as a whole if needed.

I just had a look at the help for the DIV function, does it always naturally round down?

Cheers,

J

[michaelk]

Hey James

Yes

(11 DIV 2 = 5 and 11 MOD 2 = 1)

MK

[James Russell]

Awesome!

I guess rounding up could be achieved quite easily with =(x+1) DIV 1 anyways.

;p

Thanks.

J

[michaelk]

Sort of like the Truncate function one wishes was available in worksheets....