Thomas Wagensommerer Posted June 5, 2018 Share Posted June 5, 2018 Is it possible to show zero values as empty in a worksheet? Quote Link to comment
0 Gadzooks Posted June 5, 2018 Share Posted June 5, 2018 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. 1 Quote Link to comment
0 Thomas Wagensommerer Posted June 5, 2018 Author Share Posted June 5, 2018 Thank you very much Gadzooks. I should have asked: "Is it possible to show zero values as empty in a worksheet database?" Quote Link to comment
0 Gadzooks Posted June 5, 2018 Share Posted June 5, 2018 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. Quote Link to comment
0 Thomas Wagensommerer Posted June 5, 2018 Author Share Posted June 5, 2018 (edited) 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 June 5, 2018 by Thomas Wagensommerer Deleted double picture Quote Link to comment
0 Thomas Wagensommerer Posted June 5, 2018 Author Share Posted June 5, 2018 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) Quote Link to comment
0 Gadzooks Posted June 5, 2018 Share Posted June 5, 2018 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 Quote Link to comment
0 Gadzooks Posted June 5, 2018 Share Posted June 5, 2018 I think we now ought to see your image resplendent without zeros! 😀 Quote Link to comment
0 Tim Harland Posted July 30, 2018 Share Posted July 30, 2018 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 Quote Link to comment
0 Gadzooks Posted July 31, 2018 Share Posted July 31, 2018 (edited) 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. ----------------------------------- 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... 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 July 31, 2018 by Gadzooks Extra Info Quote Link to comment
0 Tim Harland Posted July 31, 2018 Share Posted July 31, 2018 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 Quote Link to comment
0 Gadzooks Posted August 1, 2018 Share Posted August 1, 2018 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. Quote Link to comment
0 Tim Harland Posted August 1, 2018 Share Posted August 1, 2018 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 Quote Link to comment
0 Tim Harland Posted August 1, 2018 Share Posted August 1, 2018 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! Quote Link to comment
0 Gadzooks Posted August 1, 2018 Share Posted August 1, 2018 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 🤓 Quote Link to comment
0 unearthed Posted February 6, 2023 Share Posted February 6, 2023 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), '') 1 Quote Link to comment
Question
Thomas Wagensommerer
Is it possible to show zero values as empty in a worksheet?
Link to comment
16 answers to this question
Recommended Posts
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.