attempted to divide by zero

hi,

i had this formula written for a textbox in a table, but yet still encounter the following error:

expression:

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))

error:

attempted to divide by zero.

any way i can solve this problem?

thanks!

IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible. Try the following expression instead:
=IIf( CountDistinct(Fields!room.Value) = 0, 0, Sum(Fields!rate.Value) / iif(CountDistinct(Fields!room.Value) = 0, 1, CountDistinct(Fields!room.Value)))

In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))

You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.

-- Robert

|||thanks a lot, Robert.|||

Hello Robert,

Thanks for this post.This helps me a lot.

I have tried on many sites to get help but not getting much

Thanks again

Sunil Pawar.

|||This worked for me good work on this
|||It would be good if we had a VBA function to do this. This is a common requirement. And a time waster until I found this post.|||

HI Everyone,

I generally use the following statement

=IIF(Fields!Profit.Value<>0, Fields!Profit.Value/ Fields!Sales.Value, Nothing)

For the most part this formula is simple and effective...

BUT (there is always a but!!)

I received an error message "attempted to divide by zero". I checked the tables to validate column formatting and everything appears to be okay (decimals(11,2) on both columns. If any one has any suggestions, it would be greatly appreciated.

Regards,

A.Akin

|||Hi,
Please try out with this formula,

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/IIF(countdistinct(Fields!room.Value))=0,1,countdistinct(Fields!room.Value))

I think it will work.

Cheers,

Shri

|||Can Someone help me with this one I have the same issue.
= IIF(Fields!Cash_Resolution.Value = 0 Or Fields!Amount.Value = 0,0,
Sum(Fields!Cash_Resolution.Value)/ Sum(Fields!Amount.Value))

attempted to divide by zero

hi,

i had this formula written for a textbox in a table, but yet still encounter the following error:

expression:

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))

error:

attempted to divide by zero.

any way i can solve this problem?

thanks!

IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible. Try the following expression instead:
=IIf( CountDistinct(Fields!room.Value) = 0, 0, Sum(Fields!rate.Value) / iif(CountDistinct(Fields!room.Value) = 0, 1, CountDistinct(Fields!room.Value)))

In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))

You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.

-- Robert

|||thanks a lot, Robert.|||

Hello Robert,

Thanks for this post.This helps me a lot.

I have tried on many sites to get help but not getting much

Thanks again

Sunil Pawar.

|||This worked for me good work on this
|||It would be good if we had a VBA function to do this. This is a common requirement. And a time waster until I found this post.|||

HI Everyone,

I generally use the following statement

=IIF(Fields!Profit.Value<>0, Fields!Profit.Value/ Fields!Sales.Value, Nothing)

For the most part this formula is simple and effective...

BUT (there is always a but!!)

I received an error message "attempted to divide by zero". I checked the tables to validate column formatting and everything appears to be okay (decimals(11,2) on both columns. If any one has any suggestions, it would be greatly appreciated.

Regards,

A.Akin

|||Hi,
Please try out with this formula,

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/IIF(countdistinct(Fields!room.Value))=0,1,countdistinct(Fields!room.Value))

I think it will work.

Cheers,

Shri

Attempted to divide by zero

I have the following expression as a Calculated Field:
=IIf(Fields!PROJ_Y.Value <> 0 Or Not Fields!PROJ_Y.Value Is Nothing,
((Fields!PROJ_Y.Value-Fields!ACTUAL_Y1.Value)/Fields!PROJ_Y.Value),
0)
and I get the error "The Value expression for the field â'GROWTH_Yâ' contains
an error: Attempted to divide by zero." when I run the report. Obviously the
If statement is written to avoid the division by zero, so I am not sure how
this would happen.
BJI was able to resolved this issue by writing a custom function to handle the
division instead of the IIf statement.
BJ
"bjkaledas" wrote:
> I have the following expression as a Calculated Field:
> =IIf(Fields!PROJ_Y.Value <> 0 Or Not Fields!PROJ_Y.Value Is Nothing,
> ((Fields!PROJ_Y.Value-Fields!ACTUAL_Y1.Value)/Fields!PROJ_Y.Value),
> 0)
> and I get the error "The Value expression for the field â'GROWTH_Yâ' contains
> an error: Attempted to divide by zero." when I run the report. Obviously the
> If statement is written to avoid the division by zero, so I am not sure how
> this would happen.
> BJ|||I think you just needed to replace the 'Or' with an 'And'
~ Magendo_man
"bjkaledas" wrote:
> I was able to resolved this issue by writing a custom function to handle the
> division instead of the IIf statement.
> BJ
> "bjkaledas" wrote:
> > I have the following expression as a Calculated Field:
> >
> > =IIf(Fields!PROJ_Y.Value <> 0 Or Not Fields!PROJ_Y.Value Is Nothing,
> > ((Fields!PROJ_Y.Value-Fields!ACTUAL_Y1.Value)/Fields!PROJ_Y.Value),
> > 0)
> >
> > and I get the error "The Value expression for the field â'GROWTH_Yâ' contains
> > an error: Attempted to divide by zero." when I run the report. Obviously the
> > If statement is written to avoid the division by zero, so I am not sure how
> > this would happen.
> >
> > BJ

attempted to divide by zero

hi,

i had this formula written for a textbox in a table, but yet still encounter the following error:

expression:

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))

error:

attempted to divide by zero.

any way i can solve this problem?

thanks!

IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible. Try the following expression instead:
=IIf( CountDistinct(Fields!room.Value) = 0, 0, Sum(Fields!rate.Value) / iif(CountDistinct(Fields!room.Value) = 0, 1, CountDistinct(Fields!room.Value)))

In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))

You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.

-- Robert

|||thanks a lot, Robert.|||

Hello Robert,

Thanks for this post.This helps me a lot.

I have tried on many sites to get help but not getting much

Thanks again

Sunil Pawar.

|||This worked for me good work on this
|||It would be good if we had a VBA function to do this. This is a common requirement. And a time waster until I found this post.|||

HI Everyone,

I generally use the following statement

=IIF(Fields!Profit.Value<>0, Fields!Profit.Value/ Fields!Sales.Value, Nothing)

For the most part this formula is simple and effective...

BUT (there is always a but!!)

I received an error message "attempted to divide by zero". I checked the tables to validate column formatting and everything appears to be okay (decimals(11,2) on both columns. If any one has any suggestions, it would be greatly appreciated.

Regards,

A.Akin

|||Hi,
Please try out with this formula,

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/IIF(countdistinct(Fields!room.Value))=0,1,countdistinct(Fields!room.Value))

I think it will work.

Cheers,

Shri

Attempted to divide by zero

Hello.
I'm having a little bit of a problem and i have no clue what's going on.
May be somebody can explain me how the following expression could generate the
"Attempted to divide by zero" error. I would really appreciate good advice.

Briefly about report itself - one dataset, on single table with three groups
this is a thid one. Grouping works just fine, but SUM/SUM in a footer of group #3
gives an error. Datatypes: JTD_Hours is decimal(18,2), JTD_Dollars is money.
None of the fields is NULL, all nulls converted to Zeros on dataset level.
Dataset created as result of stored procedure.

Here it is:
=IIf( Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 0, Sum(Fields!JTD_Dollars.Value,"table1_CostCode")/Sum(Fields!JTD_Hours.Value,"table1_CostCode"))

Thanks,
Konstantin

IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible.

Try the following expression instead:
=IIf( Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 0, Sum(Fields!JTD_Dollars.Value,"table1_CostCode") / iif(Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 1, Sum(Fields!JTD_Hours.Value,"table1_CostCode")))

In general, you want a pattern like this to avoid division by zero:

=iif(B=0, 0, A / iif(B=0, 1, B))

-- Robert

|||

Robert,
I appreciate your response.
Your tip really helped and now i see why it didn't work.
but i would have to admit that it's kind of wrong way how IIf works but it could be just me.
Anyhow, many thanks for you advice.

Konstantin

P.S.
It seems to me make more sence to create a custom function in a code section something like XdivY(x, y, whenYIsZero) so i can reuse this code over and over again.

|||IIF is a function call like any other function call in the VB runtime library. Function arguments always get evaluated before the function body is executed. That's how all programming environments work. It would be nice to support the IF - ELSE - ENDIF statements, but the VB runtime library has only support for function calls. Given that limitation, adding a custom function like you suggested is a good approach if you need this kind of division throughout the report.

-- Robert|||

The previous posts helped me a lot. I am new to coding and have the same problem but when I am trying to divide the totals on for a group. This is the code that is currently being used.

=Iif(ReportItems!Total_GALLONS1.Value > 0,ReportItems!Total_REVENUE1.Value/ReportItems!Total_GALLONS1.Value, 0)

Any help would be appreciated.

Thanks!

|||

You should do exectly the same as in second post here by Robert Bruckner MSFT

Your statement should be like

=Iif(ReportItems!Total_GALLONS1.Value > 0,ReportItems!Total_REVENUE1.Value/IIF(ReportItems!Total_GALLONS1.Value=0,1,ReportItems!Total_GALLONS1.Value), 0)

Attempted to divide by zero

Hello.
I'm having a little bit of a problem and i have no clue what's going on.
May be somebody can explain me how the following expression could generate the
"Attempted to divide by zero" error. I would really appreciate good advice.

Briefly about report itself - one dataset, on single table with three groups
this is a thid one. Grouping works just fine, but SUM/SUM in a footer of group #3
gives an error. Datatypes: JTD_Hours is decimal(18,2), JTD_Dollars is money.
None of the fields is NULL, all nulls converted to Zeros on dataset level.
Dataset created as result of stored procedure.

Here it is:
=IIf( Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 0, Sum(Fields!JTD_Dollars.Value,"table1_CostCode")/Sum(Fields!JTD_Hours.Value,"table1_CostCode"))

Thanks,
Konstantin

IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible.

Try the following expression instead:
=IIf( Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 0, Sum(Fields!JTD_Dollars.Value,"table1_CostCode") / iif(Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 1, Sum(Fields!JTD_Hours.Value,"table1_CostCode")))

In general, you want a pattern like this to avoid division by zero:

=iif(B=0, 0, A / iif(B=0, 1, B))

-- Robert

|||

Robert,
I appreciate your response.
Your tip really helped and now i see why it didn't work.
but i would have to admit that it's kind of wrong way how IIf works but it could be just me.
Anyhow, many thanks for you advice.

Konstantin

P.S.
It seems to me make more sence to create a custom function in a code section something like XdivY(x, y, whenYIsZero) so i can reuse this code over and over again.

|||IIF is a function call like any other function call in the VB runtime library. Function arguments always get evaluated before the function body is executed. That's how all programming environments work. It would be nice to support the IF - ELSE - ENDIF statements, but the VB runtime library has only support for function calls. Given that limitation, adding a custom function like you suggested is a good approach if you need this kind of division throughout the report.

-- Robert|||

The previous posts helped me a lot. I am new to coding and have the same problem but when I am trying to divide the totals on for a group. This is the code that is currently being used.

=Iif(ReportItems!Total_GALLONS1.Value > 0,ReportItems!Total_REVENUE1.Value/ReportItems!Total_GALLONS1.Value, 0)

Any help would be appreciated.

Thanks!

|||

You should do exectly the same as in second post here by Robert Bruckner MSFT

Your statement should be like

=Iif(ReportItems!Total_GALLONS1.Value > 0,ReportItems!Total_REVENUE1.Value/IIF(ReportItems!Total_GALLONS1.Value=0,1,ReportItems!Total_GALLONS1.Value), 0)