Recreating Excel file in Alteryx

[credlinr]

I'm trying to re-create an excel file in Alteryx, however, i am getting stuck with a few formulas that exist in Excel but not in Alteryx. 

The formula i am struggling with is in column Q:

=IF(OR(INT(4*AM4/AG4)*AG4=0, O4>4), N4, (INT(4*AM4/AG4)*AG4))

As well as column S:

=IF(IF(INT(J4)=J4,H4,FLOOR(H4,AH4))=0,CEILING(H4,AH4), FLOOR(H4, AH4))

I managed to re-create the formula in column N which was:

=IF(INT(I4)=I4,H4,CEILING(H4, AG4)) through using: 

IF ( IsInteger([# Outers])) THEN [Adjusted Qty] ELSE Round([Adjusted Qty], [Outer]) ENDIF

I have attached the spreadsheet to the question. 

[T_Willins · Accepted solution]

Hi @credlinr,

Yes, these can be done in Alteryx.  Column S can be done in a single formula, but it is easier to follow in two in the same Formula tool.  The first solves your inside IF statements, the second uses the results of the inside IF statement to solve your outer IF statement.

Column Q is solved in the last formula.  All of these are in a single Formula tool.

[credlinr]

Hi @T_Willins, 

I have found an issue where the 1st Round of Outer i have created isn't coming up with the correct value as well which is causing issues for the rest of the formulas. Can i ask why you put -1 in the first formula below? 

Essentially from Column I to AD are all created manually in the below spreadsheet from the original spreadsheet i have also attached. 

The plan is to create a workflow where we will be able to enter any sheet in this format and it will spit out the results using the same workflow. 

[T_Willins]

Hi @credlinr,

The -1 is equal to True (0 = False), so IsInteger([# of Layers]) will return -1 if the field # of Layers is an integer and 0 if it is not.