Tricky formula: is this even possible?

Hello,

I have a spreadsheet where I need to calculate the following expression:

trickyformula.png

Currently I do it with a VBA macro or I can calculate it using a few cells that I hid afterwards, but is it even possible to calculate this with a single formula?

m and n are integers with m>=n and P is a real with 0 < P < 1.

Any help is appreciated!

Regards.

Post your excel file containing the formulas or VBA macro, not the mathematics formula, as it can be easier to provide a solution.

Please try

=SUMPRODUCT(COMBIN(B2,ROW(INDIRECT(B3&":"&B2))),B4^ROW(INDIRECT(B3&":"&B2)),(1-B4)^(B2-ROW(INDIRECT(B3&":"&B2))))

Bloody hell, seems to work like a charm Let me spend the next few hours of my life trying to figure out how that works... I had to change the "," by ";" but other than that seems to be spot on.

Many, MANY thanks Bo_Ry

Is it too much to ask for a brief summary of how you tackled the issue? After a quick inspection I am still a bit confused...

The goal is to create a set of number from n to m, eg; 2,3,4

=ROW(INDIRECT(B3&":"&B2))
=ROW(INDIRECT("2:4")) indirect change text to cell reference
=ROW(2:4) ROW gives row# of referecne cell
={2;3;4}

I read that as the probability of having n to m successes in m trials, which is ...

	A	B	C
1	Name	P-301 A/B
2	m	4
3	n	2
4	P	0.90
5	Block P	0.9963	B5: =1 - BINOM.DIST(n - 1, m, p, TRUE)