# What to do in worksheets when there are zero values

## Recommended Posts

If you have a worksheet with values in Column A and values in Column B then an equation in Column C (Column A / Column B) the values in Column C dont work if there is a zero value in Column A or B.

For Example:

Column A Column B Column C

10 2 (A/B) = (10/2) = 5

20 0 (A/B) = (20/0) = #DIV 0!

30 4 (A/B) = (30/4) = 7.5

Total =sum(A1..A3) = 5

In the total Row the result should be 5+0+7.5=12.5

But since one value in Column B is 0 sum stops adding at second row

Actually, if I remember my college calculus (and there is a good chance that I don't)...

5+ lim x>0 (20/x) + 7.5 =

5 + infinity + 7.5 =

something very big (or infinity + 12.5).

If you really want to make the zero denominator behave like a zero numerator, try something like this:

Column C=(SQRT(INT(A3/(B3-0.01)))*SQRT(INT(A3/(B3-0.01)))+INT(A3/(B3-0.01)))*0.5

HTH

Michael

Edited by michaelk

How about just a simple If statement coupled with a small adder to prevent the divide by zero error. Try this formula in Column C

=IF((B3<>0), (A3/(B3+0.00000001)), 0)

The If statement by itself should be enough, but it still generates the error, thus the need for the very small adder to prevent the error.

Pat

That's the difference between a mathematics and an engineering degree.

Michael

Yes, the Vectoworks worksheet stops calculating when it encounters an unexptected value.

So if it encounters a text string within a column which is being summed...it just stops adding...so the user can't use "none" in place of "0" or "O" in place of "0."

never mind that there's not even a button for summing a range, even the equations are fragile and there's no error trapping.

Cool thanks everyone

Have just been experimenting with the "IF" command and I am getting funny results.

Have just tried the equation in cell C17:

=IF(B17=0,0,B17)

In words:

=IF(B17 is 0 then show "0" in cell C17, if its not 0 then show the value from B17.

Once I hit return the cell says "False"

When I go back to the cell to change the equation line the equation line simply says:

=C17=0

Can someone explain whats going on?

Edited by CS1

Have also tried:

=IF(B17=0,'yea','nah')

and after I hit enter cell shows as "False"

and when I go back to change the equation it has gone and all it says is =C17=0

Yeah, it is a bug. That is part of the reason that I added the .000001 value into the formulas.

Pat

Ah ha thanks Pat I didnt understand your example equation at first but now it is crystal clear, thanks for your time.

Andre,

Please make sure to file the bug with vwx file.

CS1 etc...

i dived into this one because i use IF a lot

and hadn't noticed this weirdness before...

in VW 12.5.3 on xp

first thing...

=IF(B1=0,0,B1)

cell says "False"

and then the formula changes to "=C1=0"

second thing...

this boggled me, so i hit F1 for help

to find that firefox 3.0 doesn't work

with VW 12.5.3 help... AAAAAARGH!!!!!

can't get the navigation panel to appear,

flip to IE tab and it works fine....

thanks a lot, firefox 3.0!

third thing...

the 'help' example on the IF function

shows a second set of brackets!

=IF((B1=0),0,B1)

and then it works fine...

Very cool. A much more elegant solution than using the old square root of a square trick.

So the syntax of the IF command is

=IF((condition)[first comma = 'then']result1[second comma = 'else']result2) ?

Do you ever need ( ) around the result1 or result2 if they are not simple statements?

ie. =IF((B1=0),((3.14*A1)/2),(SQRT((A1/B1)*(A1/B1))*-1))

I'm trying to understand the difference between

=IF((B1=0), 0, A1/B1)

and

=IF(B1=0, 0, A1/B1)

Michael

the only difference is that one way it works, the other way it doesn't. and if it doesn't work slap brackets around it...

Based on my limited tests, it also does the calculation for both the Then and Else portions before checking to see if the condition applies.

This means that you can't use the IF statement (by itself) to isolate a calculation that might include a divide by zero error.

Pat

• Vectorworks, Inc Employee

This is not a bug. Boolean statements within an if statement must be in parentheses.

You should write: =IF((B17=0),0,B17)

Hugues

NNA

• Vectorworks, Inc Employee
Very cool. A much more elegant solution than using the old square root of a square trick.

So the syntax of the IF command is

=IF((condition)[first comma = 'then']result1[second comma = 'else']result2) ?

Do you ever need ( ) around the result1 or result2 if they are not simple statements?

ie. =IF((B1=0),((3.14*A1)/2),(SQRT((A1/B1)*(A1/B1))*-1))

I'm trying to understand the difference between

=IF((B1=0), 0, A1/B1)

and

=IF(B1=0, 0, A1/B1)

Michael

Hi Michael,

You don't need parenthesis around result1 or result2.

You only need parenthesis for Boolean statements within an IF statement.

Also note that you need to place quotation marks around text within an if statement.

Thanks

Hugues

NNA

Thanks, Hugues

Was Pat correct in his thinking that:

" ...it also does the calculation for both the Then and Else portions before checking to see if the condition applies.

This means that you can't use the IF statement (by itself) to isolate a calculation that might include a divide by zero error."

Michael

• Vectorworks, Inc Employee

Hi Michael,

Yes, that's true. Pat is right!

I'll see if this behavior can be changed.

Thanks

Hugues

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

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

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

×
×

• KBASE
• #### MARIONETTE

×
• Create New...