Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count...Sum...Faster...improvemens...; Have a good end of YEAR
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:C 5;C7)<2;COUNT (D1:D5;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(D1:D5;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 (D1:D5;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/arch...mance-monitor/ Have a great end of year , how all of you are : great ! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count...Sum...Faster...improvemens...; Have a good end of YEAR
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(D1:D5,D7)<2,SUM(E1:E5,E7)<2))),"WRON G") If this helps, please click the yes button Cheers, Shane Devenshire "ytayta555" wrote: 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:C 5;C7)<2;COUNT (D1:D5;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(D1:D5;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 (D1:D5;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/arch...mance-monitor/ Have a great end of year , how all of you are : great ! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count...Sum...Faster...improvemens...; Have a good end of YEAR
On 30 Noi, 00:20, Shane Devenshire
wrote: 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(D1:D5,D7)<2*,SUM(E1:E5,E7)<2))),"WRO NG") 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count total month per year | Excel Discussion (Misc queries) | |||
How do I get the filter mode to show the good row count? | Excel Worksheet Functions | |||
Worksheet looks good in print, not so good on-screen | Excel Discussion (Misc queries) | |||
Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) | |||
Count returns for a period - year | Excel Worksheet Functions |