CS1 Posted June 17, 2008 Share Posted June 17, 2008 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 Quote Link to comment
michaelk Posted June 17, 2008 Share Posted June 17, 2008 (edited) 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 June 17, 2008 by michaelk Quote Link to comment
Pat Stanford Posted June 17, 2008 Share Posted June 17, 2008 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 Quote Link to comment
michaelk Posted June 17, 2008 Share Posted June 17, 2008 That's the difference between a mathematics and an engineering degree. Michael Quote Link to comment
brudgers Posted June 17, 2008 Share Posted June 17, 2008 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. Quote Link to comment
CS1 Posted June 17, 2008 Author Share Posted June 17, 2008 (edited) 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 June 17, 2008 by CS1 Quote Link to comment
CS1 Posted June 17, 2008 Author Share Posted June 17, 2008 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 Quote Link to comment
Pat Stanford Posted June 17, 2008 Share Posted June 17, 2008 Yeah, it is a bug. That is part of the reason that I added the .000001 value into the formulas. Pat Quote Link to comment
CS1 Posted June 18, 2008 Author Share Posted June 18, 2008 Ah ha thanks Pat I didnt understand your example equation at first but now it is crystal clear, thanks for your time. Quote Link to comment
Jeffrey W Ouellette Posted June 18, 2008 Share Posted June 18, 2008 Andre, Please make sure to file the bug with vwx file. Quote Link to comment
gScott Posted June 18, 2008 Share Posted June 18, 2008 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... Quote Link to comment
michaelk Posted June 18, 2008 Share Posted June 18, 2008 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 Quote Link to comment
gScott Posted June 18, 2008 Share Posted June 18, 2008 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... Quote Link to comment
Pat Stanford Posted June 18, 2008 Share Posted June 18, 2008 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 Quote Link to comment
Vectorworks, Inc Employee Hugues Posted June 18, 2008 Vectorworks, Inc Employee Share Posted June 18, 2008 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 Quote Link to comment
Vectorworks, Inc Employee Hugues Posted June 18, 2008 Vectorworks, Inc Employee Share Posted June 18, 2008 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 Quote Link to comment
michaelk Posted June 18, 2008 Share Posted June 18, 2008 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 Quote Link to comment
Vectorworks, Inc Employee Hugues Posted June 18, 2008 Vectorworks, Inc Employee Share Posted June 18, 2008 Hi Michael, Yes, that's true. Pat is right! I'll see if this behavior can be changed. Thanks Hugues Quote Link to comment
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.