Formula to rid of DIV/o!

"Toby Erkson" wrote...
Easier for newbies to understand what you're talking about when you complete
the expression with the "efficiency": =IF(N(B2)=0,"",A2/B2)

Better still for newbies to rise above needing to be spoon fed. Still, I suppose
I should have spelled out that I meant this for the first argument to the IF
function.

Also, I question the value of "efficient" coding, particularly on small code
blocks. . . . It also has the negative effect of making the code more
difficult to understand/read when SOMEBODY ELSE has to take over! . . .

Agreed to a point. The advantage of N(B2)=0 compared to OR(B2="",B2=0) is that
if one really must guard against text that appears blank, either zero length
strings or arbitrary strings of ASCII and/or nonbreaking spaces, then what's
really needed is OR(TRIM(SUBSTITUTE(B2,CHAR(160),""))="",B2=0), and at that
point the claimed advantages of spelling out precisely what you're guarding
against would have become rather obscured in the mechanics of doing so. If you
believe that arbitrary strings of spaces aren't commonly used to 'blank' entry
cells, you lack real world experience writing models other people use.

. . . (Remember that year 2000 stuff? How many programmers suddenly were
doing overtime having to make corrections and trying to figure out where on
somebody else's code...man, I really felt for those COBOL dudes!) . . .

Not particularly relevant. The main problem with legacy code and Y2K was finding
the hard-coded 2 position references to years in date fields or stand-alone year
fields. Given the way COBOL variables must be declared, it was always pretty
straightforward to identify date and year variables and fields. What wasn't
always clear was how year was parsed from date fields, and the real work was
table conversion and the new/added logic to handle the converted tables.

By contrast, avoiding divide by zero errors (the central issue in this thread)is
a mainstay of numeric programming. Using N(B2)=0 as a test introduces only the N
function into the standard approach, and the use of the N function to trap text
as well as numbers could (and should) be part of the implementation
documentation for the workbook.

There's the real problem. Few take spreadsheets seriously as programming, so
there's seldom any implementation documentation. Well, if one's going to ignore
software engineering best practices, one needs to get bit in the butt enough
times to change one's mind. So, if there were documentation, use of N here would
be a nonissue.

--
To top-post is human, to bottom-post and snip is sublime.

On Fri, 30 Apr 2004 00:00:33 GMT, Harlan wrote:
Better still for newbies to rise above needing to be spoon fed. Still, I suppose
I should have spelled out that I meant this for the first argument to the IF
function.

It's just that some stuff isn't obvious to a beginner, be they new to programming and/or to Excel. I understood what you meant by "condition" but a beginner
likely wouldn't. Heck, I had no idea what N(x) was until I searched the Help...doh! Well, I learned something new :-)

Agreed to a point. The advantage of N(B2)=0 compared to OR(B2="",B2=0) is that
if one really must guard against text that appears blank, either zero length
strings or arbitrary strings of ASCII and/or nonbreaking spaces, then what's
really needed is OR(TRIM(SUBSTITUTE(B2,CHAR(160),""))="",B2=0), and at that
point the claimed advantages of spelling out precisely what you're guarding
against would have become rather obscured in the mechanics of doing so. If you
believe that arbitrary strings of spaces aren't commonly used to 'blank' entry
cells, you lack real world experience writing models other people use.

Okay, I understand your point of view -- damn good example! -- and I've been programming since Commodore came out with the Vic-20 ;-) so I am VERY aware of
this:
Build an idiot-proof application and the company will hire a better idiot who will somehow find a way to bust your code (and they won't be a QA person)!

What scares me the most is that these idiots also have a drivers license...

Not particularly relevant. The main problem with legacy code and Y2K was finding
the hard-coded 2 position references to years in date fields or stand-alone year
fields. Given the way COBOL variables must be declared, it was always pretty
straightforward to identify date and year variables and fields. What wasn't
always clear was how year was parsed from date fields, and the real work was
table conversion and the new/added logic to handle the converted tables.

My first job out of college was programming COBOL (some version earlier than I was taught in college...uhg) so I know its syntax. My friend's father worked for
the city and he had to do this stuff...he wasn't a happy camper.

By contrast, avoiding divide by zero errors (the central issue in this thread)is
a mainstay of numeric programming. Using N(B2)=0 as a test introduces only the N
function into the standard approach, and the use of the N function to trap text
as well as numbers could (and should) be part of the implementation
documentation for the workbook.

Though I understood the central issue and saw its resolution, I've noticed before where people have mentioned the use of "efficient coding" and that is what I
was commenting on.

There's the real problem. Few take spreadsheets seriously as programming, so
there's seldom any implementation documentation. Well, if one's going to ignore
software engineering best practices, one needs to get bit in the butt enough
times to change one's mind. So, if there were documentation, use of N here would
be a nonissue.

Agreed. I do include a "Notes" tab at the end of the workbook with instructions/definitions for my end users.

Toby Erkson
Oregon, USA

Final comments with regard to 'efficiency'. If speed or system resource usage is
a critical factor, you shouldn't be using a spreadsheet. In the context of
spreadsheets, it's generally best to minimize the chance for errors, and one way
of doing that is using as few expressions as possible to produce the desired
result. Thus N(x)=0 test beats OR(x="",x=0). Also, given the limitation on
nested function calls, the flatter the formula the better. Thus N(x)=0 whips the
stuffing out of OR(TRIM(SUBSTITUTE(x,CHAR(160),""))="",x=0).

Getting cute, it's possible x could be "2", in which case =5/x would evaluate to
a number, and the previous OR conditions would allow it but the N condition
wouldn't. If x could contain numeric strings, then the safest way to trap divide
by zero while allowing upstream errors to propagate through requires a condition
like

(TYPE(x)3)-ISERROR(1/x)=0

if you want to trap x = 0 (possibly after numeric string to number conversion),
x blank or x = nonnumeric text while allowing x = numeric string converting to
soemthing other than zero to be used in the calculatio. Or use a condition like

((TYPE(-x)=1)+(TRIM(SUBSTITUTE(x,CHAR(160),""))="")0)-ISERROR(1/x)=0

if you want to treat whitespace as 'blanks' but otherwise allow nonnumeric text
to propagate as #VALUE! errors. I try not to avoid ISERROR because it's overly
broad. Generally, it's a very GOOD thing to see errors since more often than not
they indicate logic errors or corruption.

--
To top-post is human, to bottom-post and snip is sublime.

Hi

i have the same problem, though my formula is a little more complex:

=(SUMPRODUCT(--(('All sign ups'!$C$1:$C$3000=$A2)+('All sign
ups'!$C$1:$C$3000=$B2)+('All sign ups'!$C$1:$C$3000=$C2)),--('All sign
ups'!$J$1:$J$3000=2),--(('All sign ups'!$G$1:$G$3000=1)+('All sign
ups'!$G$1:$G$3000=2))))/$F2

Is there no setting in excel where you can just say if anything every
devides by zero, just to return a blank?
How would i write this formula so that the cell was just blank if F had
a 0 in it?

Thanks

Hannah


---
Message posted from http://www.ExcelForum.com/

"hannahmadsen " wrote...
....
=(SUMPRODUCT(--(('All sign ups'!$C$1:$C$3000=$A2)
+('All sign ups'!$C$1:$C$3000=$B2)+('All sign ups'!$C$1:$C$3000=$C2)),
--('All sign ups'!$J$1:$J$3000=2),--(('All sign ups'!$G$1:$G$3000=1)
+('All sign ups'!$G$1:$G$3000=2))))/$F2

Is there no setting in excel where you can just say if anything every
devides by zero, just to return a blank?

No.

How would i write this formula so that the cell was just blank if F had
a 0 in it?

IF($F2,YourFormulaHere,"")

Test cell that has number (0 or otherwise) by using IF, say A1, then
something like:

IF(A1=0,"",(SUMPRODUCT(--(('All sign ups'!$C$1:$C$3000=$A2)+('All sign
ups'!$C$1:$C$3000=$B2)+('All sign ups'!$C$1:$C$3000=$C2)),--('All sign
ups'!$J$1:$J$3000=2),--(('All sign ups'!$G$1:$G$3000=1)+('All sign
ups'!$G$1:$G$3000=2))))/$F2)

Dy

ok, now another of my formula is =if($k2,$k2-$j2,"")
but it still returns #value if they are both blank or 0.
how do i get a column to just subtract one number form another from its
respective columns, but if there is not data in 1 or 2 of the cells, to just
put a blank cell?
thanks!!


"Harlan Grove" wrote in message
...
"hannahmadsen " wrote...
...
=(SUMPRODUCT(--(('All sign ups'!$C$1:$C$3000=$A2)
+('All sign ups'!$C$1:$C$3000=$B2)+('All sign ups'!$C$1:$C$3000=$C2)),
--('All sign ups'!$J$1:$J$3000=2),--(('All sign ups'!$G$1:$G$3000=1)
+('All sign ups'!$G$1:$G$3000=2))))/$F2

Is there no setting in excel where you can just say if anything every
devides by zero, just to return a blank?

No.

How would i write this formula so that the cell was just blank if F had
a 0 in it?

IF($F2,YourFormulaHere,"")

Hi
then use
=if(AND($k2,$j2),$k2-$j2,"")

--
Regards
Frank Kabel
Frankfurt, Germany


Hannah Madsen wrote:
ok, now another of my formula is =if($k2,$k2-$j2,"")
but it still returns #value if they are both blank or 0.
how do i get a column to just subtract one number form another from
its respective columns, but if there is not data in 1 or 2 of the
cells, to just put a blank cell?
thanks!!


"Harlan Grove" wrote in message
...
"hannahmadsen "
wrote... ...
=(SUMPRODUCT(--(('All sign ups'!$C$1:$C$3000=$A2)
+('All sign ups'!$C$1:$C$3000=$B2)+('All sign
ups'!$C$1:$C$3000=$C2)), --('All sign ups'!$J$1:$J$3000=2),--(('All
sign ups'!$G$1:$G$3000=1) +('All sign ups'!$G$1:$G$3000=2))))/$F2

Is there no setting in excel where you can just say if anything
every devides by zero, just to return a blank?

No.

How would i write this formula so that the cell was just blank if F
had a 0 in it?

IF($F2,YourFormulaHere,"")

"Frank Kabel" wrote...
then use
=if(AND($k2,$j2),$k2-$j2,"")
....
Hannah Madsen wrote:
ok, now another of my formula is =if($k2,$k2-$j2,"")
but it still returns #value if they are both blank or 0.
how do i get a column to just subtract one number form another from
its respective columns, but if there is not data in 1 or 2 of the
cells, to just put a blank cell?

Always better to look for causes then to try trial & error treating the
symptoms. If K2-J2 returns an error, then what does that imply are the
contents of K2 or J2 or both? Either one or the other or both are text or
error values themselves. If K2 were ="" or anything returning the same
value, =IF(K2,1,0) would return a #VALUE! error (try it!). For that matter,
if K2 were either ="1" or ="0" or equivalents, the previous IF formula would
still return #VALUE! (try it!). AND(K2,AnythingElseYouCouldThinkOf) would
also return a #VALUE! error. Excel just doesn't like text, even numeric
text, in boolean contexts.

If the OP's formula above is returning errors, then the formula the OP needs
is either

=IF(COUNTIF($J2:$K2,"0")=2,$K2-$J2,"")

or

=SUMPRODUCT(J2:K2,{-1,1})

"Harlan Grove" schrieb im Newsbeitrag
...
...
Always better to look for causes then to try trial & error treating
the
symptoms. If K2-J2 returns an error, then what does that imply are
the
contents of K2 or J2 or both? Either one or the other or both are
text or
error values themselves. If K2 were ="" or anything returning the
same
value, =IF(K2,1,0) would return a #VALUE! error (try it!). For that
matter,
if K2 were either ="1" or ="0" or equivalents, the previous IF
formula would
still return #VALUE! (try it!). AND(K2,AnythingElseYouCouldThinkOf)
would
also return a #VALUE! error. Excel just doesn't like text, even
numeric
text, in boolean contexts.

Hi Harlan
thanks for this. Thought I'd tested it but only with real blanks. So
thanks for the correction.


If the OP's formula above is returning errors, then the formula the
OP needs
is either

=IF(COUNTIF($J2:$K2,"0")=2,$K2-$J2,"")

But this formula definetely won't help if K2 contains for example ="".
It will also return an error. Of course not on the condition part but
on the TRUE part of the If statement


=SUMPRODUCT(J2:K2,{-1,1})

This is a nice one (though it will not return "" in case of J2:K2 are
blank - which seems to be the OP's desired result).

So to add an alternative I'd use:
=IF(AND(N(J2),N(K2)),K2-J2,"")

Frank