Using FormatNumber to convert a value from a Calculation

building reports desktopintelligence
1

Hello,

I’m having an issue using the FormatNumber function. I’m trying to include some error handling in a formula so if one value is 0 then the formula displays “NA”. To do this I have been trying to use the FormatNumber function. After many attempts to try to incorporate it into my original variable I have gone to the two variable approach. These are the formulas for my two variables.

Lift for Sub Loan # 30 days

=If ((<# of Sub Loans Prior 30 Days> Where (<Cell Type>="N")) = 0 Or (<# of Sub Loans Prior 30 Days> Where (<Cell Type>="Y"))= 0) Then 0 Else (((<# of Sub Loans Post 30 Days> Where (<Cell Type>="N"))- (<# of Sub Loans Prior 30 Days> Where (<Cell Type>="N")))/(<# of Sub Loans Prior 30 Days> Where (<Cell Type>="N")) - ((<# of Sub Loans Post 30 Days> Where (<Cell Type>="Y"))- (<# of Sub Loans Prior 30 Days> Where (<Cell Type>="Y")))/(<# of Sub Loans Prior 30 Days> Where (<Cell Type>="Y")))

and

Formatted Lift for Sub Loan # 30 Days

= FormatNumber(<Lift for Sub Loan # 30 days> , "###.0%")

I have tried incorporating the FormatNumber function in to the original variable and it still returned 0.0%.

Once I have this working I planned to add “If Where ( = “Y”) = 0 Then “NA” Else”

Why isn’t the FormatNumber Function giving me my number? :reallymad: Any help is appreciated.

Thanks,

mlydecker (BOB member since 2004-03-18)

2

Try putting your first formula into the report and see what values are being returned. Your decimal may be off to the point that formatnumber is just rounding off your number to 0.0%. If so, try multiplying your result by 100 or whatever it takes to make it appear as you want.

Or, leave it as a number and use the number format to display as desired. You can handle your NA by having having your formula return null and doing a custom number format where undefined is dispalyed as NA.

If you leave off an “else” condition, the else value will be null. Example:

=If = “A” Then

If doesn’t equal A, the cell will be empty and the undefined number format will be displayed.

Sandy Smyth :us: (BOB member since 2002-08-19)

3

Thanks so much! I forgot to multiply by 100. I was relying on the Percent format to do that when it was a number.

I am also looking into your suggestion to let the format take care of the NA but I think I have to set the variable to Null and I can’t figure out how to accomplish that. Any ideas?

mlydecker (BOB member since 2004-03-18)