How to eliminate conversion error in formula

Question

Hi everybody,

I am running the below attached workflow with the attached input.

What I am trying to do is to generate an output exclusively with the differences between “current” and “previous” and have them grouped by API.

The workflow I use does this correctly. However, I get a conversion error in the formula section saying “(cell data) lost information in conversion”.

This comes from the formula:

“if isnumber(tonumber([Current])-tonumber([Previous])) then

tonumber([Current])-tonumber([Previous])

else "0"

endif”

I perfectly understand why this shows up. I am using “tonumber” for a cell that does not convert into a number.

I would just like to know if there a better formula I can use that does the same but won’t give me a “conversion error”

Thank you in advance, feel free to contact me if anything I explained remains unclear

Input Conversion Formula Question.xlsx

Conversion Formula Question.yxmd

Guccio-Gasparrini · Answer

Thank you very much for this answer. However, your formula has a problem.

It considers all rows with a "." as texts and thereby ignores them when calculating differences.

I would need a modification that doesn't consider "." as text.

thank you

AngelosPachis · Answer

Hi @Guccio-Gasparrini ,

As you correctly noticed that conversion error is because in some columns you have letters and you are trying to apply a ToNumber function.

A way to remove that conversion error is simply to avoid entering that if statement if either of the columns [Current], [Previous] has a letter in them.

One way to do that is to use a REGEX_COUNTMATCHES function, which will count how many times a certain pattern appears within each cell. \D stands for non numeric characters, so in our occasion that means how many non digits appear in each cell.

As you can see from the image below, I have applied that Regex function on the [Current] column and I am getting a new column called Count of letters and for the first record it gives me the value of 2 because I have J and H in 21JH90357.

So if you nest that If function you have used in another if function that checks if the cells contain any letters, that will remove the conversion errors.

Hope that helps, let me know if that worked for you.

Cheers,

Angelos

Conversion Formula Question_2.yxmd