none
remove NaN value and replace with "-"

    Question

  • hi all,

    how to get rid of NaN values and replace them with "-" ?

    i've get rid of Infinity value, but the NaN value's still there.

    below's my formula:

    iif(((Unit2007 - Unit2006) / Unit2006.Value) * 100 = "Infinity" or ((
    Unit2007 - Unit2006) / Unit2006.Value) * 100 = 0, "-", ((Unit2007 - Unit2006) / Unit2006.Value) * 100)

    the NaN value's still shown in reports..

    help..

    thanks!
    Addin


    Friday, November 14, 2008 8:15 AM

Answers

  • See this blog posting for more details division by zero and the IIF function call behavior of evaluating all arguments: http://blogs.msdn.com/bwelcker/archive/2006/09/26/End-of-Amnesia-_2800_Avoiding-Divide-By-Zero-Errors_2900_.aspx

     

    You may want to try the following expression for your case:

    =iif(Fields!Unit2006.Value = 0, "-", ((Fields!Unit2007.Value - Fields!Unit2006.Value) / iif(Fields!Unit2006.Value = 0, 1, Fields!Unit2006.Value) * 100)

     

    -- Robert

    Monday, November 17, 2008 1:44 AM
  • I'd recommend to create a small custom code function that performs the division and handles the division by zero case.  You then have to call this function from all the textboxes in your table where you need it.  Alternatively, if the calculation just involves simple fields (no aggregates) of the same row, you can look into doing this either in the query or as a calculated field on the dataset, and use that new field in the textboxes.

    HTH,
    Robert
    Robert Bruckner   http://blogs.msdn.com/robertbruckner

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Friday, March 27, 2009 3:50 PM

All replies

  •  

    Hi,

     

    Just change ur previous query like this......

     

     

     

    =iif(Unit2006.Value=0,"-",(Unit2007 - Unit2006) / Unit2006.Value) * 100)

     

     

    Regards,

    Thiyagu

    Friday, November 14, 2008 9:26 AM
  • =iif((Unit2007 - Unit2006) = 0 OR Unit2006 = 0, "-", ((Unit2007 - Unit2006) / Unit2006.Value) * 100)

     
    =iif((VAL(Unit2007) - VAL(Unit2006) = 0 OR VAL(Unit2006) = 0, "-", ((VAL(Unit2007) - VAL(Unit2006)) / VAL(Unit2006.Value)) * 100)   

     

    Friday, November 14, 2008 1:47 PM
  • See this blog posting for more details division by zero and the IIF function call behavior of evaluating all arguments: http://blogs.msdn.com/bwelcker/archive/2006/09/26/End-of-Amnesia-_2800_Avoiding-Divide-By-Zero-Errors_2900_.aspx

     

    You may want to try the following expression for your case:

    =iif(Fields!Unit2006.Value = 0, "-", ((Fields!Unit2007.Value - Fields!Unit2006.Value) / iif(Fields!Unit2006.Value = 0, 1, Fields!Unit2006.Value) * 100)

     

    -- Robert

    Monday, November 17, 2008 1:44 AM
  • Hello,

    I have the same question as the one in the first post. The solution provided was the one I used, but I have a really big table with lots of cells with this type of calculus. I need to prevent "Infinity" and "NaN" in all of them, so my question is: do I have to do this in every single cell of the table, or is there any other way to do it in a more efficient way?

    Thanks in advance
    Friday, March 27, 2009 12:16 PM
  • I'd recommend to create a small custom code function that performs the division and handles the division by zero case.  You then have to call this function from all the textboxes in your table where you need it.  Alternatively, if the calculation just involves simple fields (no aggregates) of the same row, you can look into doing this either in the query or as a calculated field on the dataset, and use that new field in the textboxes.

    HTH,
    Robert
    Robert Bruckner   http://blogs.msdn.com/robertbruckner

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Friday, March 27, 2009 3:50 PM
  • I'd recommend to create a small custom code function that performs the division and handles the division by zero case.  You then have to call this function from all the textboxes in your table where you need it.  Alternatively, if the calculation just involves simple fields (no aggregates) of the same row, you can look into doing this either in the query or as a calculated field on the dataset, and use that new field in the textboxes.

    HTH,
    Robert
    Robert Bruckner   http://blogs.msdn.com/robertbruckner

    This posting is provided "AS IS" with no warranties, and confers no rights.


    Robert, thank you so much for your suggestion. I did like you recommended and created a small and simple function that does exactly what I want which made my "life" way easier :)
    Monday, March 30, 2009 8:45 AM