Count...Sum...Faster...improvemens...; Have a good end of YEAR

[ytayta555]

HI all , and a good end of year

I try to explain a history : I have a hobby with lotto
draws , and I made an VBA program which autogenerate
some combinations ;
first time , I was must to built myself this formulas (
millions of them ... long stuff here .. I don't fall in more
details ) , but , then , I have found the way to autogenerate
this functions ( every function being a combination ) ;

now , only problem is to have the speedest formula
which to calculate in fastest mode ( lot stuff and here ...
I don't fall in more details , too ) ;

First , I had this formula :
( 1 ) = AND(COUNT(A1:A5;A7)<2;COUNT(B1:B5;B7)<2;COUNT(C1:C5;C7)<2;COUNT
(D15;D7)<2;COUNT(E1:E5;E7)<2)

Thanks to great Harlan Grove , I get the perfect equivalent for
this formula , an array formula :
( 2 ) =AND(MMULT(TRANSPOSE(ROW(A1:E7))^0;ISNUMBER(A1:E7)*{1|1|1|1|1|0|
1})<2)
( with ctrl_sh_enter , and in my local sintax ) , and in US formula
sintax
=AND(MMULT(TRANSPOSE(ROW(A1:E7))^0,ISNUMBER(A1:E7)*{1;1;1;1;1;0;1})
<2)
{ This formula was very good for me in that time ... when I must to
made myself millions of functions ( hard to describe .. doesn't
matter .. ) } ;

With first ( 1 ) formula I was able to do the query in a database of
250.000.000 combinations ; second formula , from great Harlan,
being an array formula , work slower ;

Then , I have found another formula , which work faster then first :
= AND(SUM(A1:A5;A7)<2;SUM(B1:B5;B7)<2;SUM(C1:C5;C7)<2;SUM(D15;D7)
<2;SUM(E1:E5;E7)<2)
(I realised that I can use SUM instead of COUNT , and I found that
work faster then COUNT function ) ; with this new formula , my
database was of 400.000.000 function ;

Well , last week , in a morning , I get up from sleep ( really!!)
with a new solution : a new kind of formula ;
this is my nocturne brain produce :

= IF(AND(SUM(A1:A5;A7)<2;SUM(B1:B5;B7)<2);AND(SUM(C1:C5;C7)<2;SUM
(D15;D7)<2;SUM(E1:E5;E7)<2);"WRONG")

Indeed , it work faster than previous formula , because it
calculate the second AND function only if first AND function is TRUE
(if the condition of IF function is satisfied ) ;
In this way , now , my database is of 550.000.000 functions

* * *
Can somebody find a better solution , a better kind of formula ?
Can for this functions to make an equivalent useing MATCH
function ? Why I ask this ? an ideea from here , comment
of Doug Jenkins :
http://www.dailydoseofexcel.com/archives/2007/09/18/performance-monitor/

Have a great end of year , how all of you are : great !

 

[Shane Devenshire]

Hi,

This formula gives 3 results TRUE, FALSE and WRONG. Is that really what you
are trying to do?

Following your same logic this formula works faster because if any test
fails, none of the other ones are done.

=
IF(SUM(A1:A5,A7)<2,IF(SUM(B1:B5,B7)<2,IF(SUM(C1:C5,C7)<2,IF(SUM(D15,D7)<2,SUM(E1:E5,E7)<2))),"WRONG")

If this helps, please click the yes button

Cheers,
Shane Devenshire

 

[ytayta555]

This formula gives 3 results TRUE, FALSE and WRONG.  Is that really what you
are trying to do?

Yes , because , if first IF function condition is true I know this ,
because return
me the second result , the result of second AND function ; if in
cell , the
value is WRONG , I know that the result cann't be what I'm looking
for , because first condition doesn't realised ;

Following your same logic this formula works faster because if any test
fails, none of the other ones are done.

Indeed , it's a great idea to have 3 or 4 IF's functions

= IF(SUM(A1:A5,A7)<2,IF(SUM(B1:B5,B7)<2,IF(SUM(C1:C5,C7)<2,IF(SUM(D15,D7)<2­,SUM(E1:E5,E7)<2))),"WRONG")
Shane Devenshire

I think here is the idea for another improvement in my formula ;
I'll be so glad if you or somebody can figured and with an Match
function ...
I don't know if I ask you good and real things with this Match
function
but ... , I just think ...;
Here is deep night , I cann't work now , to tell here new results ,
but I'll come back very soon ;

Thank you so so much ;
GREAT