How to get excel formula values automatically

Questions and Help
1

how to get excel formula values automatically

is it possible to get excel formula values automatically

like

=NORMSDIST(0.95)
answer is 1.645

like that

2

Hi @swethasindhuja.k ,

At present, we do not directly support complex mathematical functions such as the cumulative normal distribution function, among others. You may refer to our list of supported mathematical functions here for more details. However, I have attempted to simulate the NORMSDIST function using the functions that are currently available.

{note}{formtext: name=num}
{get_t=z->1/(1+0.5*z)}
{get_r=z->get_t(z)*exp(-z*z-1.26551223+get_t(z)*(1.00002368+get_t(z)*(0.37409196+get_t(z)*(0.09678418+get_t(z)*(-0.18628806+get_t(z)*(0.27886807+ get_t(z)*(-1.13520398+get_t(z)*(1.48851587+get_t(z)*(-0.82215223+ get_t(z)*0.17087277)))))))))}
{exp=x->2.71828^x}
{erfcc=x->get_r(abs(x)) if x>=0 else 2-get_r(abs(x))}
{NORMSDIST=x->1-0.5*erfcc(x/(2^0.5))}
{endnote}{=NORMSDIST(num)}

1 Like
3

Thankyou so much .. it helps me but i cant understand how u create it

if possible help me to get excel formula values

=NORMSINV(0.025)
answer is -1.96

4

Hi @swethasindhuja.k ,

I also attempted to simulate the NORMSINV function and successfully achieved it by implementing a recursive algorithm. The process involved utilizing online resources and Text Blaze docs. You can take a look at the documentation for reduce and seq list functions I have used here. Additionally, I included the NORMSDIST function within the same code snippet, as it is a prerequisite for NORMSINV. It is important to note that these approximations do not yield exact values.

{note}{formtext: name=num1}{formtext: name=num2}
{get_t=z->1/(1+0.5*z)}
{get_r=z->get_t(z)*exp(-z*z-1.26551223+get_t(z)*(1.00002368+get_t(z)*(0.37409196+get_t(z)*(0.09678418+get_t(z)*(-0.18628806+get_t(z)*(0.27886807+ get_t(z)*(-1.13520398+get_t(z)*(1.48851587+get_t(z)*(-0.82215223+ get_t(z)*0.17087277)))))))))}
{exp=x->2.71828^x}
{erfcc=x->get_r(abs(x)) if x>=0 else 2-get_r(abs(x))}
{NORMSDIST=x->1-0.5*erfcc(x/(2^0.5))}
{NORMSINV=(y)->reduce(seq(1,100), 0, (x, i)->x-(NORMSDIST(x)-y)/(1/sqrt(2*3.14159) * exp(-0.5*x^2)))}
{endnote}{=NORMSDIST(num1)}
{=NORMSINV(num2)}

1 Like