Worksheet Formula Help!

[Taproot]

It's a new year, so I'm finally retooling my schedules.

In the door schedule, I'd like to automatically calculate for Energy performance, so I've set up the following:

=IF('Door'.'UserFld10'='Exterior', (Door.DoorWidth)*(Door.DoorHeight), ' ')

I've found that the single quotes seem to be optional - in my case, the above formula works. However, if the statement is false, I would prefer the formula to return a blank value (rather than a zero). My solution was to return a 'space'. Is there a better way to do this?

Now that I have the area for exterior doors only, I'm trying to multiply it by a U-value to determine a UA.

So, in the next column, I've set up:

=('Door'.'UserFld9')

In which I manually type the U-value

Then in the next column, I've entered:

=(L4*M4)

4 is the database header row

Column L= the Area

Column M= the Uvalue

The formula returns a #Value (error)

I've tried a lot of permutations and determined that any formula involving the area (column L) will return a number value. But any formula including the Uvalue (column M) will return the error. Therefore I assume that 'UserFld9' cannot handle a number value??

Please Help.

Thanks.

[Pat Stanford]

The single quotes are not necessary for the fields you are working with. Since spaces are allowed in record and field names the quotes are included because they are required for names with spaces.

Your other assumption is also correct. The UserFldX fileds are formatted as text. I do not know of any way to convert them to numbers in a worksheet. I thought that had been fixed recently, but I can't find it. I know there is no way in VW2008.

Take a look in the Resource Share- Vectorscript forum. There is a script I posted there that gives you a worksheet with all of the filed names for a selected PIO. It also lists that field type. Perhaps you can find one of the other fields that is formatted as a number that you can use for your purposes.

[Taproot]

Thanks Pat. I use your handy worksheet all the time. My version doesn't display field type, however, so you must have updated it. I'll get a copy of the new one.

After some trial and error, I repurposed the 'Door.LintelDrop' field. It took some figuring - Since all of the door number fields are hard coded as feet & inches, you have to correct for it manually (like this):

=(Door.LintelDrop)*12