Excel Statistical Functions

[leonuk10]

Hi all,

The answer I know is 312 x 311 x 310 x 309 x 308 = 2,862,766,146,240 = 2.86277E+12. The arguments are 312 and 5.

Using the notation myfunction(x,y) the result is (x)*(x-1)*(x-2)*(x-3)*(x-4)*..etc...*(x-(y+1))

Another example, myfunction(17,10) = 70572902400 (17x16x15x14x13x12x11x10x9x8).

What is myfunction? I'm looking for a excel formula I could use within a spreadsheet without the use of VBA.. I know I could write a solution using VB and creating my own formula, BUT is there already a built in function in Excel I could use, for speed?

My maths/stats knowledge is somewhat limited but I know it might have something to do with the factorial function =FACT(X)/(FACT(Y-X) or using the BINOMDIST somehow? Or is there another more suitable function?

The FACT(X) etc would work but usually I'd be using this using very large numbers so will often result in an overflow error!

Help!! Thanks in advance.

 

[shg]

It would be

=FACT(A1)/FACT(A1-B1)

... but for the fact that you can only compute factorials up to 170. So, instead,

=PRODUCT(A1 - (ROW(INDIRECT("1:" & B1))-1))

... confirmed with Ctrl+Shift+Enter

 

[leonuk10]

It would be

=FACT(A1)/FACT(A1-B1)

... but for the fact that you can only compute factorials up to 170. So, instead,

=PRODUCT(A1 - (ROW(INDIRECT("1:" & B1))-1))

... confirmed with Ctrl+Shift+Enter

Fantastic! Yes, I managed to get myself to =FACT(A1)/FACT(A1-B1) but, as mentioned, would not work for very large numbers. I do like the formula you used though, didn't occur to me to use array functions - it's not something I use often or familiar with. Quite scared in fact to use ctrl+shift+enter!

Many thanks for that, big help. Also encouraged me to learn more about array formulas as this is a weakness of mine!! Very interesting.

 

[shg]

You're welcome, glad it worked for you.

 

[leonuk10]

Long shot, but don't ask you'll never learn...

What is the BINOMDIST function? I guess it stands for binomial distribution and is within this field of mathematics... but thats as far as my knowledge goes.

Think this is more of a maths question than an excel question though!

 

[cornflakegirl]

On the original question - myfunction is actually just PERMUT - eg PERMUT(17,10) = 70572902400

(You are calculating permutations - the number of ways you can select 10 things from a pool of 17 where order matters).

 

[cornflakegirl]

The binomial distribution is about things like flipping coins where the probability of success is independent for each trial.

So if you wanted the probability of exactly 1 head if you flipped a coin 3 times, you would use

BINOMDIST(1,3,0.5,FALSE) = 0.375
(There are 2x2x2=8 possible outcomes of three coin flips, 3 of them give exactly 1 head, so the probability is 3/8 = 0.375)

 

[shg]

myfunction is actually just PERMUT - eg PERMUT(17,10) = 70572902400

Argh; missed that. Nice recognition.