Formula Tool - Issue

[JDong]

Hi Team,

I get the below error with this IF statement...how to overcome this issue

IF Status = 'Yes' and [percent] = '100.00%' THEN '0.00%'
ELSEIf Status = 'No' THEN [percent] / [Total_percent]
else [percent]
ENDIF

Thanks

[Jonathan-Sherman · Accepted solution]

Hi @JDong,

My guess is your issue is with the "THEN  [percent] / [Total_percent]" part of your formula as these fields are likely strings looking at the error message.

Perhaps change your formula to

IF Status = 'Yes' and [percent] = '100.00%' THEN '0.00%'
ELSEIf Status = 'No' THEN ToString((ToNumber([percent]) / ToNumber([Total_percent])*100), 2) + '%'
else [percent]
ENDIF

If this solves your issue please mark the answer as correct, if not let me know!

Regards,

Jonathan

[vishnuharinarayanan]

Hi ,

Can you check the  data type of the column where you are applying the numeric operator, as the error message says that Numeric Operator is applied to a String Value?

The column Percent seems to be of String Data Type.

Numeric operators can be applied only to columns in the Number format ( either be Double, fixed decimal etc.)

Please let me know if the issue is resolved.

[RolandSchubert]

Hi @JDong ,

I think, the error is caused by the [percent] column. '100.00%' seems to be a string data type, in your formula you try to calculate using this string.

A few changes should solve the problem - try:

IF Status = 'Yes' AND [percent] = '100.00%' THEN
'0.00%'
ELSEIf Status = 'No' THEN
ToNumber(Replace([percent], '%', '')) / [Total_percent]
ELSE
ToNumber(Replace([percent], '%', ''))
ENDIF

If [Total_percent] is also string, you should use ToNumber as well.

Best,

Roland

[JDong]

Hi @Jonathan-Sherman 

It says unknown variable ToNumber

The percent field is a fixed decimal. Thanks

[JDong]

@RolandSchubert 

When I do this get an error

Thanks

[Jonathan-Sherman]

Hi @JDong,

Could you share a sample of the data? Or a mock up - with your formula?

Regards,

Jonathan 

[JDong]

IF Status = 'No' and [percent] = '100.00%' THEN '0.00%'
ELSEIf Status = 'Yes' THEN ToNumber(Replace([percent], '%', '')) / ToNumber(Replace([Total_percent], '%', ''))
else ToNumber(Replace([percent], '%', ''))
ENDIF

[Jonathan-Sherman]

Where are your Status and Total_percent fields? And is your formula updating a field or creating a new column?

[JDong]

Thanks Jonathan...the above calculation works !

I had to change the datatype of percent and total percent fields.