Need to replicate Excel Math functions (NORMSDIST and NORMSINV) into Alteryx Workflow

[aparanjith1]

Hi All,

I am completely a new bee to Alteryx and I am working on converting the excel input into a Alteryx workflow. I came across the Math functions in Excel (NORMSDIST and NORMSINV) which I could not find in Alteryx Formula tool, but when I researched about it I came across links which are saying to download Addins from the third party (GIT) to get these Math functions into Alteryx. But, unfortunately that is not an option for me as my company does not allow that.

So, I am looking for some alternate approach where I can replicate the same in Alteryx workflow. Below is the Excel formula which I need to replicate in Alteryx;

=NORMSDIST(NORMSINV(E9)/SQRT(1-F9) + SQRT(F9/(1-F9))*NORMSINV(G4))

Can someone please help me in this?

Thanks in Advance!

[patrick_digan]

Do you have the alteryx predictive tools installed? It's a separate install that alteryx provides. They include an R tool which could be used in this case.

Let me know if you have the predictive tools installed, and I'll put a sample workflow together if you do.

[aparanjith1]

Hi Patrick,

I do have the predictive tools on my ALteryx Designer, and I also see the R tool in there.

Thanks!

[aparanjith1]

To be more precise, I do have the R tool in the Developer tools tab, below is the screenshot attached for it.

[patrick_digan · Accepted solution]

@aparanjith1 I've prettied it up in the attached workflow, but I think something like this formula in R should be equivalent to your excel formula:

output<-data.frame(pnorm(qnorm(variable1)/sqrt(1-variable2)+sqrt(variable2/(1-variable2))*qnorm(variable3)))

I hope that helps! Let me know if I've missed something.

[aparanjith1]

Thank you Patrick, that should work as per my initial test. Will let you know if I face any problem further.

Thanks!

[aparanjith1]

Hi Patrick,

I am trying to imply your logic into my WF and I am getting an error here. Not sure what am I doing wrong here. I am putting the columns which I have in the WF in place of your variables, and it is giving me an error saying unexpected varable1. Below is the screen shot of it, and I am also attaching the WF file.

[patrick_digan · Accepted solution]

@aparanjith1 You'll just need to change your R code to point to the right variables. For variable1, it should be:

variable1 <-data$PD.tmp.(col4)

data = the variable name we've assigned to our entire dataset

$ = R shorthand which allows us to grab 1 named column from the entire dataset

PD.tmp.(col4) = your column name after converting spaces to periods.

[aparanjith1]

Got it. Thanks for the reply Patrick.

[AdiStanescu]

Hi Patrick,

Could you please please help me with the following situationa?

I have the following situation. I try only to apply the replace formula NORMSDIST from excel...in Alteryx.

I want to apply the NORMSDIST to the collumn named  "d1_1" from the data base....but I have the issues "is a nont numeric argument..." But the collumn "d1_1" containd only number...

Thank you