Divide by Zero error

I am still getting this error. This is my formula:
=iif(ReportItems!BUDMTD_2.Value=0,0,ReportItems!MTDChange_2.value/ReportItems!BUDMTD_2.Value)
This works in another report but not here.IIF is a function call. Therefore all arguments get evaluated before IIF is
called and you see a division by zero.
Possible solutions can be:
1) Avoid 0 in divisor (consider
=iif(ReportItems!BUDMTD_2.Value=0,0,ReportItems!MTDChange_2.value /
iif(ReportItems!BUDMTD_2.Value = 0, 1,ReportItems!BUDMTD_2.Value ))
2) Write VB function SafeDivide (A,B) and call it when needed (search
"SafeDivide" in this group)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <David@.discussions.microsoft.com> wrote in message
news:2FC621DF-8C78-480F-A9E6-512A438B75B5@.microsoft.com...
>I am still getting this error. This is my formula:
> =iif(ReportItems!BUDMTD_2.Value=0,0,ReportItems!MTDChange_2.value/ReportItems!BUDMTD_2.Value)
> This works in another report but not here.|||David,
just in case it helps...I got so tired of writing statements like this
and trying to test them (because you finish up writing a lot of them)
that I wrote my division routines into a custom assembly and called the
custom assembly...the custom assembly then gives me much more control
over how I deal with bad data in calculations...
Peter
www.peternolan.com

Divide by Zero

Dear all,

I have a formula as below:

=switch(A=0,"Black",B/switch(A,1,A)*100>0,"Black",B/switch(A,1,A)*100<0,"Red")

I use this formula to get rid of the "divide by zero" error, anyway, i get another error which isArgument 'VarExpr' is not a valid value instead of

Attempted to divide by zero after applying the above formula.

Does anyone have any ideas?

Thanks

Levine

Hi,

From your description, it seems that you met the "divided by zero" in your report, right?

If so, I think you should use IIF function to check the value first, and then, decide if it could be divide in the expression. See the following code:

=IIF(Fields!Deno.Value = 0, "N/A", (Fields!MyType.Value) / IIf(Fields!Deno.Value = 0, 1, Fields!Deno.Value))

Thanks.

Divide by Zero

When I specify a formula between Computed Column Specification, I have two
zero values, getting Divide by Zero error, any idea how can I avoid this? I
still want SQL Server to display Zero if it is 0/0, is this possible in SQL
Server database?
Thanks
J.CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 INT NOT NULL
)
INSERT INTO #Test VALUES (50,0)
INSERT INTO #Test VALUES (20,10)
INSERT INTO #Test VALUES (0,0)
SELECT *,
CASE WHEN col1>0 AND col2>0 THEN col1/col2 ELSE 0 END FROM #Test
"Joriv" <nojunk@.please.com> wrote in message
news:dumr51$6id$1@.reader01.news.esat.net...
> When I specify a formula between Computed Column Specification, I have two
> zero values, getting Divide by Zero error, any idea how can I avoid this?
> I still want SQL Server to display Zero if it is 0/0, is this possible in
> SQL Server database?
> Thanks
> J.
>|||use case, here is an example
create table #test (value1 numeric (12,2),value2 numeric (12,2))
insert into #test
select 1,0 union all
select 1,0 union all
select 5,3 union all
select 4,2
select case value2 when 0 then 0 else value1/value2 end as SomeValue
from #test
http://sqlservercode.blogspot.com/|||Joriv,
Since 0/0 is not equal to zero, you need to make a different
calculation to get what you want. No programming language
should have a setting to display wrong answers (0 for the result of
0/0), so you have to be specific about what you want. One way
to do this in SQL is
CASE WHEN bottomValue = 0 THEN 0 ELSE topValue/bottomValue END
You don't say whether or not you also want a result of 0 if you have
1/0, 2/0 and so on. The example above will give you a result of 0
in these cases as well.
Steve Kass
Drew University
Joriv wrote:

>When I specify a formula between Computed Column Specification, I have two
>zero values, getting Divide by Zero error, any idea how can I avoid this? I
>still want SQL Server to display Zero if it is 0/0, is this possible in SQL
>Server database?
>Thanks
>J.
>
>|||Thanks Folks for all your responses, even I think I can use isnull,
but the idea is i want to use this Computed Column Spec in table designer,
how can I insert a formula which does this? even when i do this
isnull((table.column1/table.column2), 0), still does not work...getting same
error...any clue?
thanks
J.
"Joriv" <nojunk@.please.com> wrote in message
news:dumr51$6id$1@.reader01.news.esat.net...
> When I specify a formula between Computed Column Specification, I have two
> zero values, getting Divide by Zero error, any idea how can I avoid this?
> I still want SQL Server to display Zero if it is 0/0, is this possible in
> SQL Server database?
> Thanks
> J.
>|||Hi
CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 AS CASE WHEN col1>0 AND col2>0 THEN col1/col2 ELSE 0 END
)
INSERT INTO #Test VALUES (50,0)
INSERT INTO #Test VALUES (20,10)
INSERT INTO #Test VALUES (0,0)
SELECT * FROM #Test
"Joriv" <nojunk@.please.com> wrote in message
news:dumteu$7at$1@.reader01.news.esat.net...
> Thanks Folks for all your responses, even I think I can use isnull,
> but the idea is i want to use this Computed Column Spec in table designer,
> how can I insert a formula which does this? even when i do this
> isnull((table.column1/table.column2), 0), still does not work...getting
> same error...any clue?
> thanks
> J.
> "Joriv" <nojunk@.please.com> wrote in message
> news:dumr51$6id$1@.reader01.news.esat.net...
>|||Thanks for the group,
Now I run into another problem, once I set this formula successfully, I
cannot change the value programmatically. anyway I can do this?
Actually what I want to do this, Initally (default) I want to set to a
formula (say col1/col2) but I might change this programmatically in future
if required.
I tried to set this formual in the default/binding value, but I don't think
it likes any formual's over there.
Any ideas?
Thanks
J.
"Joriv" <nojunk@.please.com> wrote in message
news:dumr51$6id$1@.reader01.news.esat.net...
> When I specify a formula between Computed Column Specification, I have two
> zero values, getting Divide by Zero error, any idea how can I avoid this?
> I still want SQL Server to display Zero if it is 0/0, is this possible in
> SQL Server database?
> Thanks
> J.
>|||Here is the query without case/when.
e.g.
select isnull(a/nullif(b,0),0)[div]
from (select 1 a, 0 b
union all select 2,1)x
-oj
"Joriv" <nojunk@.please.com> wrote in message
news:dumteu$7at$1@.reader01.news.esat.net...
> Thanks Folks for all your responses, even I think I can use isnull,
> but the idea is i want to use this Computed Column Spec in table designer,
> how can I insert a formula which does this? even when i do this
> isnull((table.column1/table.column2), 0), still does not work...getting
> same error...any clue?
> thanks
> J.
> "Joriv" <nojunk@.please.com> wrote in message
> news:dumr51$6id$1@.reader01.news.esat.net...
>|||>> Actually what I want to do this, Initally (default) I want to set to a fo
rmula (say col1/col2) but I might change this programmatically in future if
required. <<
Then use VIEWs instead of a computed column, which is portable, which
can be drop and which allows you to have multiple formulas.
What answer did you want for 0/0? I would go with a NULL or catch the
error.

Divide by Zero

When I specify a formula between Computed Column Specification, I have two
zero values, getting Divide by Zero error, any idea how can I avoid this? I
still want SQL Server to display Zero if it is 0/0, is this possible in SQL
Server database?

Thanks
J.use case, here is an example

create table #test (value1 numeric (12,2),value2 numeric (12,2))
insert into #test
select 1,0 union all
select 1,0 union all
select 5,3 union all
select 4,2
select case value2 when 0 then 0 else value1/value2 end as SomeValue
from #test

http://sqlservercode.blogspot.com/|||IF like this:
Update a
Set X=column b/column c
where Column c<>0

Update a
Set X=0
where Column c=0

any help to you?|||Or perhaps something like:

Update a
Set X= case when columnc=0 then 0 else column b/column c end

MC

"yangyang" <loveflying000@.gmail.com> wrote in message
news:1141834929.055768.75450@.v46g2000cwv.googlegro ups.com...
> IF like this:
> Update a
> Set X=column b/column c
> where Column c<>0
> Update a
> Set X=0
> where Column c=0
> any help to you?