Recreating Excel file in Alteryx

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.

NFM rounding logic combined 4 mths stock 21.05 2nd rounding of layer changed.xlsx

Workflow

Input

Preparation

Accepted answers

T_Willins

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.

Recreate Excel Formulas.png

Recreate Excel Formulas.yxmd

All comments

T_Willins

Hi @credlinr,

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

Recreate Excel Formulas.png

Recreate Excel Formulas.yxmd

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.

NFM skul summary original.xlsx

NFM rounding logic combined 4 mths stock 21.05 2nd rounding of layer changed.xlsx

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335