Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Using Formula and If Statements



I am very new to Alteryx and to be honest not the best at using formulas on excel. I am trying to write an If statement formula on Alteryx with multiple possible results. I.e If in the Column "Grade" we select "A1" then this new formula column will populate with "80", if we select "SA" then the cell populate with "190" etc. However when I have attempted this I am presented with the error message "Formula: invalid type in subtraction operator". Is this due to the fact that the grade field is currently set as data type "V String" and I want my new formula column to be numeric so I have set it as double, or is it an issue with how I have written the formula?


Here is the current formula I have created: 


IF [Grade] = "A1" THEN "80" ELSE IF [Grade] = "SA" THEN "190" ELSE IF [Grade] = "Contractor" THEN "34" ELSE IF [Grade] = "M" THEN "240" ELSE IF [Grade] - "SM" THEN "300" ELSE "?" ENDIF ENDIF ENDIF ENDIF ENDIF


Can anyone advise me as how to best sort this error? I require the output to be in numeric format.





Alteryx Certified Partner

Hi @pmaguire011 this syntax would work if your new column is a string field


IF [Grade] = "A1" THEN "80"

ELSEIF [Grade] = "SA" THEN "190"

ELSEIF [Grade] = "Contractor" THEN "34"

ELSEIF [Grade] = "M" THEN "240"

ELSEIF [Grade] - "SM" THEN "300"

ELSE "?"



If you want a numeric field then the syntax should be 


IF [Grade] = "A1" THEN  80

ELSEIF [Grade] = "SA" THEN 190

ELSEIF [Grade] = "Contractor" THEN 34

ELSEIF [Grade] = "M" THEN 240

ELSEIF [Grade] - "SM" THEN 300




You Grade field should be a string field in the formula tool when you create a new field you want to select a numeric field type.


Hi @pmaguire011 


By typing quote marks around the numbers you want to return, you're returning text instead. Take out the quote marks around the numbers. You'll also need to use something else in place of the ? for the rest, so below I've replaced that with a zero. For fields to be numeric, all possibilities must be numbers


You don't need to nest IF THEN ELSE, because Alteryx has IF THEN ELSEIF....



Try this and let me know if it works (note that ELSEIF is one word not two)

IF [Grade] = "A1" THEN 80

ELSEIF [Grade] = "SA" THEN 190

ELSEIF [Grade] = "Contractor" THEN 34

ELSEIF [Grade] = "M" THEN 240

ELSEIF [Grade] - "SM" THEN 300






Hi @EstherB47


This worked perfectly, thanks for that, this will be really helpful for me with similar issues!