# Worksheet Database Math

## Recommended Posts

Hi All,

Trying to create a custom field in a database that does some simple math for me.  The current string is ='Multicable VW'.'Break Out 1'/'Multicable VW'.'Voltage Delivered'

Essentially, it's supposed to divide the value of "Breakout 1" by the value of "Voltage Delivered".  The result I am getting in the database is #VALUE!

What am I doing wrong?  Is there a resource that describes how to tackle math in a database?

99% chance that one or both of the fields is not a number.

Is Multicable VW a PIO? If so, then place a cable (I recommend in a new blank drawing) and leave it selected and run the script in the following thread. It will generate a worksheet that will tell you the type of every field in the PIO record.

If it is not a PIO but rather a custom record, check the record format and make sure everything is formatted as a number.

Alternatively, try simpler formulas of ='Multicable VW'.'Break Out 1'+1 and ='Multicable VW'.'Voltage Delivered'+1 to determine if either field is formatted as numbers.

For Doors/Windows/Spaces, they have multiple fields with some formatted as numbers and others formatted as strings for display. Perhaps the Multicable has this also. If so (and it is a PIO), the worksheet generated by the script will give you a list of every field.

HTH

@Pat Stanford Yeah, its a PIO.  I'm positive the 'Multicable VW'.'Break Out 1" field is not defined as a number field.  This field populates with data it receives from a lighting device, which, depending on the user, isn't always a number.  I set the multi cable object to receive the wattage of the lighting device.  It initially came in as 1200W, but I removed the "W".

Is there a way to set that field as a decimal?

The Break Out # fields are parameters that are defined as Text.  That way they can receive any thing you pick.  So your math won't work.  You will need to strip the "W" off the Breakout value and turn it into a number.  The voltage you are getting from 'Multicable VW'.'Voltage Delivered' is also text field parameter but it will not have any non-numeric characters in it.   @Pat Stanford you want have a go at how to best do that in a worksheet.  I would be doing it all with VS.

I did strip the non-numeric values from the lighting device field which translated on down the line.  As far as that voltage delivered values, they should populate a non-numeric value on their own, right?  Unless the field itself is defined as general and not a decimal.

I think the issue lies with the wattage parameter at the lighting device level.  You cannot change the parameter field type from "text" to "integer" in the spotlight preferences as its greyed out.  It's always going to be seen as a text field regardless of wether or not the value of field does not contain any non-numeric characters.

I'm sure you can see what I am trying to do.  Maybe there is a better way of going about this within VW?

If the fields are defined in the record as Text I believe they will be treated like text in the worksheet no matter how the cell is formatted.

The basic worksheet function to make a number out of a string that looks like a number (numbers, decimal points, MAYBE comma group separators) is the Value function.

So =Value('Multicable VW'.'Voltage Delivered') should give you a valid number.  Likewise if you have manually stripped the non-numbers from the other field Value should give you a valid number. I recommend the +1 test to make sure they are each working correctly before you try to combine them.

=Substring('Multicable VW'.'Break Out 1', 'W', 2) should give you the numeric part after a leading W character.  So =Value(Substring('Multicable VW'.'Break Out 1', 'W', 2)) should give you a number you can actually use.

If you can stand it, I actually recommend using extra columns (you can hide them later if they bother you) and store each record.field into a column and then use the cell reference to access the data. Especially for the Substring this could be important.

So set the formula in A3 to =Multicable VW'.'Break Out 1' and the formula in B3 to =Substring(A3, 'W', 2)

@Pat Stanford Thanks.  Adding VALUE to the string made it all work.  The final string is now =VALUE('Multicable VW'.'Break Out 1')/VALUE('Multicable VW'.'Voltage Delivered'). Of course this assumes that all lighting devices are stripped of non-numeric characters in the wattage field.

Just to clarify, does =Value(Substring('Multicable VW'.'Break Out 1', 'W', 2)) omit the "W" if it is before or after numerals?  The wattage field is generally populated as "1200W", not "W1200".

I used the "+1" test on both fields as you suggested.  Neither returned a value.

The next bit of the equation to throw out there is: how would you tackle "Multicable VW'.'Break Out 1' that yields a value of, for example, 600/600?  There are often situations where there are multiple lighting devices assigned to the same break out circuit.  I haven't tried it yet but how would the "/" be treated?  Would it be seen as a separator or as a "divide"?

Thanks for the help.  If I can get this hammered out I may actually make me want to use the multi cable tool, ha ha.

Substring uses a delimiter ("W" in this case) and returns the "Nth" piece of the string specified by the last parameter from the sting provided by the first parameter.

So Substring ('123W456W789W0', 'W', 1) would return 123;

Substring ('123W456W789W0', 'W', 2) would return 456

Substring ('123W456W789W0', 'W', 4) would return 0

So you need to use =Value(Substring('Multicable VW'.'Break Out 1', 'W', 1)) to get the number equivalent to the items before the W.

A slash in the string would have to be treated as a delimiter. Substring is not one of my favorite functions and trying to do to many different things is likely to be difficult. You would have to separate them out and use Value on each part of the 600/600 string separately.

@Pat Stanford Thanks for the clarification.  That makes perfect sense.

I guess for now the best/simplest option is to do the math for the circuits that have multiple lighting devices assigned to them.

Thanks again for all the help!

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×