We help IT Professionals succeed at work.

"Attempted to divide by zero" When trying to compute percentage with formula

The formula below would work but I receive a runtime error "cannot divide by zero" because it looks like even if the condition is evaluated as true it still evaluates both parts before it decides which value to return. I've read articles that say there is a workaround but I don't know how to adapt it exactly. It says you can use nested iif statements. Can someone tell me how I can adapt my formula to this
Example that should circumvent the divide by 0 issue
=IIf(Fields!SomeField.Value = 0, 0, Fields!SomeOtherField.Value / IIf(Fields!SomeField.Value = 0, 1, Fields!SomeField.Value))

Open in new window


My formula:
=iif(Fields!optiontype.Value = "BSE",(Fields!basemodelprice.Value - SUM(Fields!BudgetByProduct.Value)) / Fields!basemodelprice.Value, (Fields!amount.Value - SUM(Fields!BudgetByProduct.Value)) / Fields!amount.Value)

Open in new window

Comment
Watch Question

Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
=iif(Fields!optiontype.Value = "BSE",(Fields!basemodelprice.Value - SUM(Fields!BudgetByProduct.Value)) / iif(Fields!basemodelprice.Value=0,1,Fields!basemodelprice.Value), (Fields!amount.Value - SUM(Fields!BudgetByProduct.Value)) / iif(Fields!amount.Value=0,1,Fields!amount.Value))
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
jleecole, do you still have this issue?
J C

Author

Commented:
Thank You