# Matrix formula for 8 numbers (last delay and longest drawn)

1. ## Matrix formula for 8 numbers (last delay and longest drawn)

Hi, Everyone!

I would like to know How to make a matrix formula to Count amount of 8 ball repetitions. In my case, I know the total number of 8 balls repetitied, but I don't have a matrix formula to know how many times the game has been last drawn and longest delay.

See my spredsheet below:
Lottery_Again.JPG

Lottery_Again.xlsx

Thank you,
Rafael  Register To Reply

2. ## Re: Matrix formula for 8 numbers (last delay and longest drawn)

K2
Formula:  `Please Login or Register  to view this content.`

N2
Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Matrix formula for 8 numbers (last delay and longest drawn)

Thank You! Bo_Ry

I am trying to understand the formulas.

Regards,
Rafael  Register To Reply

4. ## Re: Matrix formula for 8 numbers (last delay and longest drawn)

Hi,

FYI Bo_Ry's formulas can be simplified to:

K2:

=LOOKUP(2,1/(MMULT(COUNTIF(A2:H2,a!\$C\$1:\$Q\$2000),ROW(A\$1:A\$15)^0)=8),a!\$A\$1:\$A\$2000)

N2:

=MAX(MMULT(AGGREGATE(15,6,ROW(a!\$C\$1:\$Q\$2000)/(MMULT(COUNTIF(A2:H2,a!\$C\$1:\$Q\$2000),ROW(Z\$1:Z\$15)^0)=8),ROW(Z\$1:INDEX(Z:Z,I2-1))+{1,0}),{1;-1}))

Neither of these requires CSE.

@Bo_Ry

You don't need CSE or an additional, coercing INDEX function in either case, even in your original formulas: MMULT is powerful enough on its own!

Regards  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 