Jump to content

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

Link to comment

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

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

Link to comment

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.

Link to comment

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

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

Link to comment

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

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

Link to comment

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

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
Reply to this topic...

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