问题:

I am trying to evaluate a Price per Kilo ($/Kg) based on sales of a product. This works fine if the product was acutally sold during the period specified. However if the product is not sold the Kg (the denominator) ends up being 0 (zero) and an error results. - Divide by Zero error.

I tried this

=iif(KgSold=0,0,Revenue/KgSold)

It appears that the iif function is calculating both the true and false results. How do I get around this.

Should I be using the switch function instead?

=switch(KgSold=0,0
        KgSold<>0,Revenue/KgSold)

回答1:

You're right, it doesn't short circuit. That sucks.

You'll have to do something like this:

= Iif(KgSold = 0, 0, Revenue) / Iif(KgSold = 0, 1, KgSold )

The switch function should also work.

回答2:

This happens because in VBScript all conditions within an IIF will be evaluated first before any functionality occurs.

回答3:

Add the following to your code:

Public Function SafeDiv(byval num as double, byval den as double) as object

    If den = nothing then return nothing
    If den = 0 then return nothing

    return num / den

End Function

Then call