# How do I use IIF to Avg - Max Points!

How do I rewrite this expression so that it ignores negitive numbers and zeros but will not return NaN... the expression below works great however if I change it to <>0 it returns NaN
``````= IIF(Fields!Profit.Value=0,nothing,(Fields!.Value/Fields!.Value))
``````
LVL 1
###### Who is Participating?

Commented:
=IIF(Fields!Profit.Value=0,nothing,IIF(Fields!.Value/Fields!.Value)<=0,nothing,Fields!.value/Fields!.Value))

basically, for the 3rd part, wrap it in another IIF, test it for <= 0, and output either nothing or the 3rd part
0

Commented:

Try this

= IIF(IsNothing(Fields!Profit.Value),0,Fields!.Value)/IIf(IsNothing(Fields!.Value) Or Fields!.Value=0,1,Fields!.Value)

0

Author Commented:
Thank you @sammySeltzer I cant get it to work, I am lost on the denominator (bottom) part... below is the actual expression
``````IIF(Fields!InvoiceAmount_CurrentYear.Value=0,nothing,(Fields!ShipmentProfit.Value/Fields!InvoiceAmount_CurrentYear.Value))
``````
0

Commented:
what do you want to show if negative?
0

Commented:
and what error did you get with the code I posted?
0

Commented:
Try changing from =0 to <= 0, so that if it is negative or 0, it goes to "nothing"

The problem with <>0 is that it causes divide by 0 on the right side
0

Commented:
Try this.
Run this function

``````Public Shared Function Ratio(Num1 as double, Num2 as double) AS object
IF ISNOTHING(Num2) Or Num2 = 0 Then
Ratio = 0
ELSEIF Num1 = 0 THEN
Ratio = 0
ELSE
Ratio = Num1  / Num2
END IF
End Function
``````

Then use this code to invoke the function:

``````=Code.Ratio(Fields!InvoiceAmount_CurrentYear.Value, Fields!ShipmentProfit.Value/Fields!InvoiceAmount_CurrentYear.Valu)
``````
0

Author Commented:
Ok I put the function in the report properties> code and I put the "invoker" in the expression... correct?

if so it indicates that the ratio portion is an unidentified expression

=Code.Ratio(Fields!InvoiceAmount_CurrentYear.Value, Fields!ShipmentProfit.Value/Fields!InvoiceAmount_CurrentYear.Valu)

0

Commented:

"How do I rewrite this expression so that it ignores negitive numbers and zeros but will not return NaN... the expression below works great however if I change it to <>0 it returns NaN"

Do you want to turn
(1) a [final] result of -ve to 0, e.g. what the [working] IIF would return as-is right now?
or
(2) change the IIF such that the first condition ignores negative and 0? It currently doesn't perform the ratio for 0 [only], but that is only because the ratio of x/0 is undefined.  You cannot divide by 0.
0

Author Commented:
I want the final result to be nothing or greater that Zero
0

Author Commented:
Thank You!
0

Commented:

Even though the thread is closed, I wanted to show you why you got undefined expression:

Fields!ShipmentProfit.Value/Fields!InvoiceAmount_CurrentYear.Valu)

Notice that I copied Fields!InvoiceAmount_CurrentYear.Valu but mistakenly cut off e.

e
I pasted Fields!InvoiceAmount_CurrentYear.Valu

0

Author Commented:
@sammySeltzer, thanks soo much, I missed that!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.