0

# #Error while using Tonumber function for a string

Feb 22 at 10:28 AM

33

I am getting #error for Measure "Budget" I converted string to number. This works for P1, P2 but not for Budget. I think this is due to the cells are empty. Can you please help me to get this correct.

rzd5t.png (31.4 kB)

This is how it looks

AMIT KUMAR
Feb 22 at 10:31 AM
0

try this.

=if(isnull([Budget]) or ([Budget]="")) then 0 else tonumber([Budget])

Show 9 Share

Hi Amit, sorry to come back to you again. Do you know what formula i need to apply to get 1 decimal place and replacing dot with comma.I am trying to apply custom format but then it changes the formatting.

Current value = 17.47

Value required = 17,4%

nitin Sood

you can try with format number and replace function.please note after that it's type will be string.

Current value = 17.47

=replace(Formatnumber([Current value];"0.0");".";",")+"%"

AMIT KUMAR

But how does this function work where I am using formula below to get current value to get 71.10 :

=If (IsError(ToNumber([Retail marge % net intake P1]))) Then 0 Else ToNumber([Retail marge % net intake P1])

Think I am doing some thing wrong here as i am using IF and Replace both to get 71,1% :

=replace(Formatnumber(If (IsError(ToNumber([Retail marge % net intake P1]))) Then 0 Else ToNumber([Retail marge % net intake P1]);"0.0");".";",")+"%"

nitin Sood

sorry , it works.

=replace(Formatnumber(If (IsError(ToNumber([Retail marge
% net intake
P1]))) Then 0 Else ToNumber([Retail marge
% net intake
P1]);"0.0");".";",")+"%"

AMIT KUMAR

Thanks a lot for your instant replies, I am very pleased that you help me very quickly. Let me know if i can do anything for you.

AMIT KUMAR

Last question again : The formatting part works well by converting 71.0 to 71,1% but when I try to put conditional formatting lets say (Greater than 1% should be GREEN and less than -1% should be RED) then it is not working. Results are that even -10,0% is showing GREEN as well. It was working before fine. Some how the conditional formatting does not recognize the numbers. Any clues ?

nitin Sood

when you are using conditional formatting create one more extra variable only for to use in conditional formatting.

Greater /less is not going to work with string data type and formatnumber convert the number to string.use below var1 for conditional formatting.

var1=If (IsError(ToNumber([Retail marge
% net intake
P1]))) Then 0 Else ToNumber([Retail marge
% net intake
P1])

in the report display use this.

=replace(Formatnumber(If (IsError(ToNumber([Retail marge
% net intake
P1]))) Then 0 Else ToNumber([Retail marge
% net intake
P1]);"0.0");".";",")+"%"

AMIT KUMAR

Thanks a lot, good work around but i have to create lot of variables and conditonal formatting rules.

nitin Sood

This is the only workaround you have..in other way you have specifc requirement to display comma instead of dot and for doing that you have to use format number.