Skip to Content
0

#Error while using Tonumber function for a string

Feb 22 at 10:28 AM

33

avatar image

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)
10 |10000 characters needed characters left characters exceeded

This is how it looks
0
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
AMIT KUMAR
Feb 22 at 10:31 AM
0

try this.

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

Show 9 Share
10 |10000 characters needed characters left characters exceeded

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%

0

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");".";",")+"%"

0

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");".";",")+"%"

0

sorry , it works.

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

1

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.

1

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 ?

0

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");".";",")+"%"

0

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

0

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.

0