Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value Error
not sure how to fix..
basically have 2 numbers want to subtract, but get DIV/0 Error, (i.e. items cannot be equal; note: i get if both equal, then nothing, but longer equation results, below needed for other calculation.. just need to fix 1st item "trying" below). thanks... trying: (finding some numbers are only different by .0001; argh) =CR383-IF(CR383-CQ383=0,0.0001,"") gets a value error trying to embed in some fasion, to keep space down, critical. else: =IF(CR383-CQ383=0, ((CS383-CQ383)/((CR383-0.0001)-CQ383)*10), ((CS383-CQ383)/(CR383-CQ383)*10)) works / is full version of problem trying to reduce to: =((CS383-CQ383)/((CR383-IF(CR383-CQ383=0,0.0001,""))-CQ383)*10) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value Error
Let's try to simplify. You have two numbers; let them be in A1 and B1 for
simplicity Now tell use what you want to compute. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "nastech" wrote in message ... not sure how to fix.. basically have 2 numbers want to subtract, but get DIV/0 Error, (i.e. items cannot be equal; note: i get if both equal, then nothing, but longer equation results, below needed for other calculation.. just need to fix 1st item "trying" below). thanks... trying: (finding some numbers are only different by .0001; argh) =CR383-IF(CR383-CQ383=0,0.0001,"") gets a value error trying to embed in some fasion, to keep space down, critical. else: =IF(CR383-CQ383=0, ((CS383-CQ383)/((CR383-0.0001)-CQ383)*10), ((CS383-CQ383)/(CR383-CQ383)*10)) works / is full version of problem trying to reduce to: =((CS383-CQ383)/((CR383-IF(CR383-CQ383=0,0.0001,""))-CQ383)*10) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value Error
hi, thanks for reply, know I may not know how to ask sometimes, but usually
certain basic math not at issue, but commands such as IF, VALUE.. for "syntax". major concern was just subtracting 2 numbers, that would be used in a division problem: did not want zero result. found answer, but curious if there is a simpler way. answer was in syntax, just needed to replace the ditto's, with a zero to get rid of value error, subsequent division works. thanks. =((CS383-CQ383)/((CR383-IF(CR383-CQ383=0,0.0001,""))-CQ383)*10) =((CS383-CQ383)/((CR383-IF(CR383-CQ383=0,0.0001,0))-CQ383)*10) "Bernard Liengme" wrote: Let's try to simplify. You have two numbers; let them be in A1 and B1 for simplicity Now tell use what you want to compute. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "nastech" wrote in message ... not sure how to fix.. basically have 2 numbers want to subtract, but get DIV/0 Error, (i.e. items cannot be equal; note: i get if both equal, then nothing, but longer equation results, below needed for other calculation.. just need to fix 1st item "trying" below). thanks... trying: (finding some numbers are only different by .0001; argh) =CR383-IF(CR383-CQ383=0,0.0001,"") gets a value error trying to embed in some fasion, to keep space down, critical. else: =IF(CR383-CQ383=0, ((CS383-CQ383)/((CR383-0.0001)-CQ383)*10), ((CS383-CQ383)/(CR383-CQ383)*10)) works / is full version of problem trying to reduce to: =((CS383-CQ383)/((CR383-IF(CR383-CQ383=0,0.0001,""))-CQ383)*10) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value Error
Hi,
=CR383-IF(CR383-CQ383=0,0.0001,"") gets a value error The error is the result of the double quotes "", replace with 0. =CR383-IF(CR383-CQ383=0,0.0001,0) as for the second part of your problem, you could use an error trap like "if(iserror(formula),"",formula)" but that would lenghten the formula not shorten it, one solution might be to make it invisible using conditinonal formatting with: =ISERROR(A1) and formatting the text white. HTH Jean-Guy "nastech" wrote: not sure how to fix.. basically have 2 numbers want to subtract, but get DIV/0 Error, (i.e. items cannot be equal; note: i get if both equal, then nothing, but longer equation results, below needed for other calculation.. just need to fix 1st item "trying" below). thanks... trying: (finding some numbers are only different by .0001; argh) =CR383-IF(CR383-CQ383=0,0.0001,"") gets a value error trying to embed in some fasion, to keep space down, critical. else: =IF(CR383-CQ383=0, ((CS383-CQ383)/((CR383-0.0001)-CQ383)*10), ((CS383-CQ383)/(CR383-CQ383)*10)) works / is full version of problem trying to reduce to: =((CS383-CQ383)/((CR383-IF(CR383-CQ383=0,0.0001,""))-CQ383)*10) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value Error
Hello Nastech,
As I read this, basically you want (CS383-CQ383)*10/(CR383-CQ383) but when the denominator ends up as zero (ie CR383=CQ383) you want (CS383-CQ383)*10/(0.0001) Your formula has extra parentheses =((CS383-CQ383)/((CR383-IF(CR383-CQ383=0,0.0001,0))-CQ383)*10) this is the same: =(CS383-CQ383)/(CR383-IF(CR383-CQ383=0,0.0001,0)-CQ383)*10 and is easier to read, I think. But a reader may misinterpret the 10, so I would use =(CS383-CQ383)*10/(CR383-IF(CR383-CQ383=0,0.0001,0)-CQ383) Here is a way without IF =(CS383-CQ383)*10/((CR383-CQ383-(CR383=CQ383)*0.0001)) The term -(CR383=CQ383)*0.0001 is evaluated thus: The term (CR383=CQ383) this is either TRUE or FALSE; but when we perform an arithmetic operation (add, subtract, multiply or divide) on a Boolean value (T or F), Excel treats TRUE as 1 and FALSE as 0 So, if the two values (CR383 and CQ383) are equal then the denominator becomes (0 - 1*0.0001) or 0.0001 By the way: the symbol " is best referred to a quote mark (or double quote). True, it is used as a ditto abbreviation in lists but Excel Help and English-speaking Excel users will call it a quote mark or quotation mark. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "nastech" wrote in message ... hi, thanks for reply, know I may not know how to ask sometimes, but usually certain basic math not at issue, but commands such as IF, VALUE.. for "syntax". major concern was just subtracting 2 numbers, that would be used in a division problem: did not want zero result. found answer, but curious if there is a simpler way. answer was in syntax, just needed to replace the ditto's, with a zero to get rid of value error, subsequent division works. thanks. =((CS383-CQ383)/((CR383-IF(CR383-CQ383=0,0.0001,""))-CQ383)*10) =((CS383-CQ383)/((CR383-IF(CR383-CQ383=0,0.0001,0))-CQ383)*10) "Bernard Liengme" wrote: Let's try to simplify. You have two numbers; let them be in A1 and B1 for simplicity Now tell use what you want to compute. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "nastech" wrote in message ... not sure how to fix.. basically have 2 numbers want to subtract, but get DIV/0 Error, (i.e. items cannot be equal; note: i get if both equal, then nothing, but longer equation results, below needed for other calculation.. just need to fix 1st item "trying" below). thanks... trying: (finding some numbers are only different by .0001; argh) =CR383-IF(CR383-CQ383=0,0.0001,"") gets a value error trying to embed in some fasion, to keep space down, critical. else: =IF(CR383-CQ383=0, ((CS383-CQ383)/((CR383-0.0001)-CQ383)*10), ((CS383-CQ383)/(CR383-CQ383)*10)) works / is full version of problem trying to reduce to: =((CS383-CQ383)/((CR383-IF(CR383-CQ383=0,0.0001,""))-CQ383)*10) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ODCB Error 1919 and ODCB Error 6 | Setting up and Configuration of Excel | |||
error bars display | Charts and Charting in Excel | |||
Error bars-repost | Charts and Charting in Excel | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
#REF error | Excel Worksheet Functions |