Jump to content
  • 0

Worksheet: Show zero values as empty


Thomas Wagensommerer

Question

16 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..

 

=IF((COMPONENTAREA(4)<>0), COMPONENTAREA(4), '')

 

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.

 

 

  • Like 1
Link to comment
  • 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

 

1842375530_ScreenShot2018-06-05at16_42_05.thumb.png.a6744f46803f2b583a5b3d029762edc7.png

 

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

Link to comment
  • 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)

 

 

Link to comment
  • 0

Excellent.

 

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!

 

HTH

 

 

 

Link to comment
  • 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?

 

Thanks,

 

Tim

Link to comment
  • 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...

 

789323462_ScreenShot2018-07-31at09_20_28.thumb.png.2fbabeba281b174bdeaae3a6ceaf8d3c.png

 

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...

 

728940044_ScreenShot2018-07-31at09_21_12.thumb.png.a0de5a2feccaa7ec5604b80f8d5773a9.png

 

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

 

2011266586_ScreenShot2018-07-31at09_21_38.thumb.png.2623bccdb088fc08bc1c92ee2fa3369c.png

 

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...

 

1986637386_ScreenShot2018-07-31at09_20_47.thumb.png.27d78a001cb6fe9eae895a710d3f18e0.png

 

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

 

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

 

946754156_ScreenShot2018-07-31at09_46_01.thumb.png.a0b741d692ed86d9d6a0f4f0829b10b6.png

 

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.

 

-----------------------------------

EDIT

 

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...

 

949315367_ScreenShot2018-07-31at10_26_57.thumb.png.363759a40174871cc5b25ca56e0df3f0.png

 

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
Link to comment
  • 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). 

193517169_AreaSchedule-SummingColumns.thumb.jpg.47a0c1c78d9ebb12e3584532e599ecd0.jpg

 

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

Link to comment
  • 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...

 

1327281338_ScreenShot2018-08-01at10_34_29.thumb.png.99c0d17a52e7b074ffa57f0e9ac60d20.png

 

(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.

 

 

 

 

Link to comment
  • 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?

 

image.thumb.png.346c4019df39d3098d2a93fae200dee6.png

Space table with Blanks.vwx

Link to comment
  • 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 🤓

 

 

Link to comment
  • 0
On 6/6/2018 at 3:38 AM, Gadzooks said:

 

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..

 

=IF((COMPONENTAREA(4)<>0), COMPONENTAREA(4), '')

 

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.

 

 

Thanks for this Gadzooks, just got this to work in a 2018 worksheeet and am very happy with all the empty spaces!

=IF((SUM(K18..M18)<>0), SUM(K18..M18), '')

  • Like 1
Link to comment

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.

Guest
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.

×
×
  • Create New...