Worksheets - Help with formulas

[Boh]

Hi, I have set up a worksheet to help estimate drawing production times and then also to run calculations on actual progress.

 

It's looking really good so far. The worksheet accesses some custom records placed on titleblocks to report projected hours to complete each drawing as well as "hours to go" and returns a "% Finished" calculation.

 

As well as calculations on the drawing sheets, it's also possible to enter projected times and hours to go for other tasks such as site measure, consultant liaison etc. (These additional tasks are just normal spreadsheet cells not database cells)

 

I have just one item I can't work out. If a task listed is not required, i.e. has "n/a" marked to it, or is left blank, or "0" is entered, then the % finished entry returns "#DIV 0!", which kind of messes up an otherwise clean and readable worksheet.

 

Does someone out there know if there is an "IF" function, or something similar that will return a blank cell or at least "0%" instead of "#DIV 0!". I have tried a standard IF function, but it looks like it neads an additional function (or better formula syntax) to work. This seems possible in excel worksheets but can't get it to work on vectorworks ones.

 

It seems like a small thing but as this worksheet is going to be used regularly on our projects I want it as readable as possible.

 

I would appreciate any help on this. Any worksheet wizards out there? 🙂

 

 

 

 

[zeroInf]

I think you must use IFERROR  please read documentation here >    IFERROR

[Boh]

Thanks for the tip @zeroInf however it looks like that function doesn't fly with vw. I've tried the exact same operations in both excel and vw: excel is great, vw is not great.

 

 

Any other ideas, suggestions?

 

Thanks/

[Pat Stanford]

I don't think there is any way in a 2019 worksheet to check if something is a number or not. Without that functionality, you are going to have the problem you have.

 

What are the formulas you are using. I can probably come up with a simple worksheet script that will check and ensure that math is only done on numbers, but before I spend too much time on that I want to make sure your formulas are reasonable to do this way.

 

[Boh]

Hi Pat.

The formulas are simple divisions. The error comes up when there is a blank cell or a 0 in the denominator. Interesting that IF function works on the database cells how I want it to but not on the spreadsheet cells.

 

This stuff is still relatively new to me- this is my most ambitious worksheet to date. I've attached a file containing the worksheet if you have time to have a look and offer some sage advice.

 

It's vw2018 but should work in 2019. (I have 2019 just not using it yet).

 

Note also that the title blocks I'm calling up in the WS are the OzCad VAA one's. I'm not using native vw titleblocks yet.

 

Thanks for your help!

Worksheet Sample Dwg.vwx

[Boh]

Hey @Pat Stanford

Did this one slip past you? I'm guessing that what I'm asking for isn't possible right? It's no big deal if so, just a nice to have so please don't spend time on it if so.

 

Thanks

[Pat Stanford]

I have been fighting this off and on for a week and can't seem to crack it.

 

Apparently, VW evaluates both of the possible outcomes of an IF statement and throws up the VALUE or DIV BY 0 error if either one fails, even if that is not the one you want.

 

I have looked at options of trying to use a combination of Value and Concat to determine is a cell has a number or not, but so far I have not found the trick.

 

If you have enough control over how this will be uses, we could work on a custom Worksheet Script that would do the proper error checking, but it will either need to be stored in ever users User Folder or Workgroup Folder or in every file. If you don't have control over the environment and how the files are used, this will probably break pretty easily.

 

 

[Boh]

Thanks Pat. Looks like we need to file this in the too hard basket. (Either that or the 'not worth the effort' basket.)

 

It' not a deal breaker, it just means the worksheets aren't as clean as I'd like them to be, they are still workable.

 

Really appreciate your efforts however!

 

Bo

[Pat Stanford]

@Boh I think I just figured out a work around that will give you what you want, but it will take a couple of extra hidden columns.

 

Put your current %Finished Calculations into a column that can be hidden.  I am going to pretend this is column AA.

 

Hours Total  is in column I (letter i)

 

In the % Finished Column put a formula of  =IF(I17<>0,AA17,0);

 

So Column AA will have the #Div 0 result, but since it is going to be hidden it does not matter.  

 

The IF in Column I will ignore the fact that one of the options is an error and will either return the error message or the alternate value.

 

If you would rather have it show the tasks with zero scheduled hours show as 100% complete change the % finished second value to 1 instead of 0.

 

You will need to have four hidden columns, one for each of the Progress checks.

 

If you don't want to have to fix the IF all the time you could do an extra hidden column with just =I17<>0 (pretend this is column AE) and then just use that cell in the IF.

 

=IF(AE17,AA17,0)

 

I hope this makes sense.

 

Now I can finally close this window in Safari 😉

 

[Boh]

THANK YOU! Yes that works great.

 

Just one other question....

 

Is it possible in VW worksheets for a function to insert text or even leave a blank into a decimal or percentage cell? E.g. I would like the cell to say "tba" if information is still to be provided.

[Pat Stanford]

Off the top of my head I would say no, but you may be able to come up with a hidden column solution for that also.

 

Do the entry somewhere else other than where you want it to display, and then use an If to pull the value in if it has been entered or display 'tba' if it still zero

 

You can certainly manually enter text into a cell that is formatted as a number, but I don't know of a way to display something other than what was entered into a cell that you allow data entry in.  So long way of saying you could populate the original version with tba, but if someone changes it, it will not automatically change back.  Unless you use the IF as discussed above.

[Boh]

OK. I think I'll trial run this worksheet as it is and then look to tidy it up a bit if needed.

 

BTW I slightly tweaked your earlier solution and nested some if statements so that if no "Hours to go" was provided then the "%Finished" would still be 0%. The yellow columns in the screen shot would be the hidden columns.