Jump to content
  • 0
Thomas Wagensommerer

Worksheet: Show zero values as empty


15 answers to this question

Recommended Posts

  • 0
10 minutes ago, Thomas Wagensommerer said:

Is it possible to show zero values as empty in a worksheet?


Sure is Thomas. I too dislike zeros on my nicely tabbed sheets.


Standard logic applies and you need an IF xxxx(Then,Else) statement.


So as an example..




Step through the logic and it will all make sense (I hope). You either get a value greater or less than zero, or the cell has a big fat 'space' printed in it.


Come back if you would like more - or PM me the sheet and I'll tidy it for you.



Share this post

Link to post
  • 0
1 minute ago, Thomas Wagensommerer said:

"Is it possible to show zero values as empty in a worksheet database?"


Yes - the formula returns exactly what you want


This is an extract from a Wall Areas Database




You will see some wall types do not have anymore than three components and therefore do not show 0 (zero) in the cell.

Share this post

Link to post
  • 0

Thank you again Gadzooks.


This is what I have. Zeros should not be displayed.




This is what I get when I try your suggestion. The database does not calculate some formulas.




Edited by Thomas Wagensommerer
Deleted double picture

Share this post

Link to post
  • 0

Thank you very much Gadzooks. You got me to try and I got it to work. I always assumed formulas like these were impossible in database rows. (Or broken - regarding my general level of trust in all kinds of Vectorworks features.)


Depending on localization you need a semicolon instead of a colon in worksheet formulas. So the formula on a localized system, when a colon is the decimal separator is


=IF (Condition ; True Value ; False Value)



Share this post

Link to post
  • 0



Yes Ive had problems with database/spreadsheet formulas (in other software) where it seemed obvious I'd picked the wrong separator, but just couldn't get it right.


Your solution is what I do in the situation -  keep trying with subtle changes until the cells suddenly reveal you've done it!






Share this post

Link to post
  • 0

Hi Gadzooks,


Do you know if it possible to then sum these empty values (for instance in Thomas's example above could you have a column with the total of all the areas)? If I have zeros then I can add them to other cells, but when I leave them empty I get a #VALUE! in the cells that I have added them to - I presume VW is treating the empty cell as text?





Share this post

Link to post
  • 0

Hi @Tim Harland, if I understand your question correctly, you want to SUM the column (parts of which would be 0 (zeros) but having used the formula to be 'tidy' are now showing as blanks)


On 7/30/2018 at 10:02 AM, Tim Harland said:

I presume VW is treating the empty cell as text?


You can still 'use' the hidden zeros.


Using the sheet I have for wall styles (used in the above thread) and listing the components...




You see the sheet finds 5 walls in the drawing and shows only two have a 'component 4' (the blanks are provided by the formula discussed in the above thread)


Your question (I believe) asks for the SUM of this column (containing blanks)


If you choose to see Database Headers...




It will reveal the totals for you at the source cell. (also seen when you are creating the database headers) So...




It shows a total of 2 components and reveals the total area.


This may have answered your question, but if you want to 'pretty' the column up, stop showing database headers and place a spreadsheet formula below the database in each column. So...




(Ive placed TOTAL AREA in the adjacent cell - this is just plain text)


The cell contains SUM with the appropriate reference cell...




or just '= J2' if you have no requirement to sum other cells


Yes - summing different component areas is not a 'normal' calc, but this worksheet was to hand and thread specific having used it above. I believe you may want to provide a solution more like Thomas' example?


Hope this helps.





Just thought I should add. Once you have this working correctly (math wise), you will want to create a formula to 'tidy' this cell as well.


For example..My cells below (still for the same 5 wall styles) for Component 7 are blank, but using the straight formula takes you back to an untidy sheet display...




You will need to use a more 'intelligent' formula to provide clean blank cells - I'll leave that to you as you have all the information you need 😎



Edited by Gadzooks
Extra Info

Share this post

Link to post
  • 0

Hi @Gadzooks 


Hi @Tim Harland, if I understand your question correctly, you want to SUM the column (parts of which would be 0 (zeros) but having used the formula to be 'tidy' are now showing as blanks) 


Not exactly - the columns sum vertically OK, I am trying to sum the rows - in your example it would be the sum of all the Component 1's rather than the sum of each wall. See an example below and .vwx attached (Column C, D and E are using IF statements as per your help above, which work great btw, your advice was invaluable). 



For the moment I have worked around it with nested IF statements, but that is quite complicated. We are just reflecting the way the Area Schedule from a particular client has been constructed so it isn't critical, although I am interested to understand why it doesn't work, given that the blank cells are essentially empty? I tried a few variations using VALUE functions but that didn't seem to help.



Space table with Blanks.vwx

Share this post

Link to post
  • 0

Mmmmm......(scratching head)


Your worksheet has a choice NF, VF or TF to display areas with certain criteria. So it's a straight choice and the 'total' (by way of only one column will ever carry a figure) is already displayed. And therefore to SUM them would just replicate the figure.


Although I do get your explaination that this is a question about "what if I wanted to...." (with a different sheet)


23 hours ago, Tim Harland said:

in your example it would be the sum of all the Component 1's


I think not. In my example, the sum of 'component 1' is delivered at the bottom of the sheet. A straight column SUM, as explained above, and already in use on your worksheet to deliver the total NF, VF or TF of the Spaces it can currently 'see'.


I think perhaps you accidentally swapped what you meant to say?


(Just as an exercise) I can provide a TOTAL to the last column as this...




(Reduced the sheet to just three components) I've highlighted the required cell boxed red. Works as an excercise, but doesn't offer anything useful to the Construction process.


Let me know if I'm on the right path.


If you want to further this, I think it would help to provide a more specific example sheet where totals both to rows and columns appear necessary, as at the moment, I can't see the reason with the sheets we are both using.





Share this post

Link to post
  • 0

Hi Gadzooks,


Your right that was perhaps a bad example - I have updated it to show you what I mean - in our actual example we have certain rooms where more than one category of area applies  and therefore the total column is relevant - we do this by summarizing the space number - that may be where the issue arises - I had to reinstate the zero value for the VF and TF columns in order for it to display correctly. Somehow it seems that Vectorworks treats the '' not as empty but as some sort of non numerical value? 


I hope that makes more sense?



Space table with Blanks.vwx

Share this post

Link to post
  • 0

Yep, tried that in 2018 and it worked - will have to look at the improvements to worksheets in 2018 in more detail - it looks much better!


Thanks for your help!

Share this post

Link to post
  • 0

Oh - sorry Tim - forgot it was an older file version you'd posted. Didn't really take that into account. Glad it moved you on a bit. 


VW worksheets should come with 'user caution'. Half the time you know what you want and what result you're expecting in cells, but you have to slog away at different options to reach the goal.  I've got my sheets set up for specific tasks and I don't usually create or modify so much, so it's just as hard for me!


Whereas there are super talented people on the forum that could calculate moon landings 🤓



Share this post

Link to post

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.

Answer this question...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

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


7150 Riverwood Drive, Columbia, Maryland 21046, USA   |   Contact Us:   410-290-5114


© 2018 Vectorworks, Inc. All Rights Reserved. Vectorworks, Inc. is part of the Nemetschek Group.

  • Create New...