Become formula.

  • Thread starter Thread starter =?gb2312?B?Q2FjdHVzIFvPycjLx/Jd?=
  • Start date Start date
?

=?gb2312?B?Q2FjdHVzIFvPycjLx/Jd?=

A B C D E
1 1 1 1 1
2 3 4 5 6
3 6 10 15 21
4 10 20 35 56
5 15 35 70 126
6 21 56 126 252
7 28 84 210 462
8 36 120 330 792
9 45 165 495 1287
10 55 220 715 2002
......


"A:A" is serial numbers.

"B1"=SUM(A$1:A1), "B2"=SUM(A$1:A2), "B3"=SUM(A$1:A3),...
"C1"=SUM(B$1:B1), "C2"=SUM(B$1:B2),...
......
"E10"=SUM(D$1:D10)

Drawing "B1" to fill other cells.


I want a formula/function, when call it by (X,Y),
Formula/Function counting that Cells(X,Y) value.
without list all process by Worksheet.

Thank.
 
One way ..

Assuming this table is in Sheet1,
cols A to E, numbers in row1 down
A B C D E
1 1 1 1 1
2 3 4 5 6
3 6 10 15 21
4 10 20 35 56
5 15 35 70 126
6 21 56 126 252
7 28 84 210 462
8 36 120 330 792
9 45 165 495 1287
10 55 220 715 2002
Click to expand...

In Sheet2
---------
Let's say the coords (X,Y)*
will be input into A1:B1
(A1 for X, B1 for Y)

*X = column# input, Y = row# input

Put in C1:

=IF(COUNTBLANK(A1:B1)<>0,"",OFFSET(Sheet1!$A$1,B1-1,A1-1))

C1 will return the results from Sheet1
depending on the inputs made in A1:B1

For example:

A1: 3, B1: 3 returns 10 (from C3 in Sheet1)
A1: 5, B1: 6 returns 252 (from E6 in Sheet1)
A1: 2, B1: 7 returns 28 (from B7 in Sheet1)

and so on
 
Thank your reply.



But I want thing may as this function.

Public Function Formula(ByVal Y As Long, ByVal X As Long) As Double
If (Y < 2) Then
Formula = 1
ElseIf (X < 2) Then
Formula = Y
Else
Formula = Formula(Y - 1, X) + Formula(Y, X - 1)
End If
End Function

That function too slow if X, Y is bigger number.
 
I am not sure what formula you are trying to do. But as a side note, you
have a Binomial table. Therefore, the intersection, given x & y, might be
something like this...

=COMBIN(X+Y-1,Y)

Given this, you may be able to shorten..
Formula = Formula(Y - 1, X) + Formula(Y, X - 1)
Click to expand...

HTH
 
Another way to say this is that if you put the following in A1.
=COMBIN(ROW()+COLUMN()-1,COLUMN())

And drag the formula down and over, you will get the same table.
However, I am not sure what your "Formula = " function is trying to do.

HTH :>)
 
"Dana DeLouis"
However, I am not sure what your "Formula = " function is trying to do.
Click to expand...

It's theory Sum of Dice numbers muster.

3 dices with number 1 to 6.
if 1-2-3, 1-3-2, 2-1-3, 2-3-1, 3-1-2, 3-2-1, as same case.

Total possible case is that formula(6,3).


Thnka you :)
 
Back
Top