Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FUNCTION in easyer format
Have a good time !
I have this function : =AND(COUNT(Z4;Z5;Z6;Z7;Z8;Z9;Z10;Z11;Z12;Z13;Z14;Z 15;Z16;Z17;Z18)<=1;COUNT(AA4;AA5;AA6;AA7;AA8;AA9;A A10;AA11;AA12;AA13;AA14;AA15;AA16;AA17;AA18)<=1;CO UNT(AB4;AB5;AB6;AB7;AB8;AB9;AB10;AB11;AB12;AB13;AB 14;AB15;AB16;AB17;AB18)<=1;COUNT(AC4;AC5;AC6;AC7;A C8;AC9;AC10;AC11;AC12;AC13;AC14;AC15;AC16;AC17;AC1 8)<=1;COUNT(AD4;AD5;AD6;AD7;AD8;AD9;AD10;AD11;AD12 ;AD13;AD14;AD15;AD16;AD17;AD18)<=1;COUNT(AE4;AE5;A E6;AE7;AE8;AE9;AE10;AE11;AE12;AE13;AE14;AE15;AE16; AE17;AE18)<=1;COUNT(AF4;AF5;AF6;AF7;AF8;AF9;AF10;A F11;AF12;AF13;AF14;AF15;AF16;AF17;AF18)<=1;COUNT(A G4;AG5;AG6;AG7;AG8;AG9;AG10;AG11;AG12;AG13;AG14;AG 15;AG16;AG17;AG18)<=1;COUNT(AH4;AH5;AH6;AH7;AH8;AH 9;AH10;AH11;AH12;AH13;AH14;AH15;AH16;AH17;AH18)<=1 ;COUNT(AI4;AI5;AI6;AI7;AI8;AI9;AI10;AI11;AI12;AI13 ;AI14;AI15;AI16;AI17;AI18)<=1;COUNT(AJ4;AJ5;AJ6;AJ 7;AJ8;AJ9;AJ10;AJ11;AJ12;AJ13;AJ14;AJ15;AJ16;AJ17; AJ18)<=1;COUNT(AK4;AK5;AK6;AK7;AK8;AK9;AK10;AK11;A K12;AK13;AK14;AK15;AK16;AK17;AK18)<=1) Can somebody give me an equivalent for this function , to be more simply ? 1.) Every count function must work like an normal Count function, not like COUNTIF function with criteria<=1 ! 2.)Like this doesn't help me (COUNT(AG4:AG18)<=1;COUNT(AK4:AK18)<=1;........... .), because this function is for exemple , I must made and built milions af this function , with references in combinatoric order ! 3.) Every count function is a combination of 15 numbers , used for an lotto statistic database , it doesn,t work to count { COUNT(AH4;AI4;AJ4;AK4)................., the drow is in an one column , not in one row . Thanks very much for your amability and for your time ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FUNCTION in easyer format
the code below is 234 characters which will work in excel. I replaced the
semicolon with commas. =AND(COUNT(Z4:Z18)<=1,COUNT(AA4:AA18)<=1,COUNT(AB4 :AB18)<=1,COUNT(AC4:AC18)<=1,COUNT(AD4;AD5:AD18)<= 1,COUNT(AE4:AE18)<=1,COUNT(AF4:AF18)<=1,COUNT(AG4: AG18)<=1,COUNT(AH4:AH18)<=1,COUNT(AI4:AI18)<=1,COU NT(AJ4:AJ18)<=1,COUNT(AK4:AK18)<=1) "ytayta555" wrote: Have a good time ! I have this function : =AND(COUNT(Z4;Z5;Z6;Z7;Z8;Z9;Z10;Z11;Z12;Z13;Z14;Z 15;Z16;Z17;Z18)<=1;COUNT(AA4;AA5;AA6;AA7;AA8;AA9;A A10;AA11;AA12;AA13;AA14;AA15;AA16;AA17;AA18)<=1;CO UNT(AB4;AB5;AB6;AB7;AB8;AB9;AB10;AB11;AB12;AB13;AB 14;AB15;AB16;AB17;AB18)<=1;COUNT(AC4;AC5;AC6;AC7;A C8;AC9;AC10;AC11;AC12;AC13;AC14;AC15;AC16;AC17;AC1 8)<=1;COUNT(AD4;AD5;AD6;AD7;AD8;AD9;AD10;AD11;AD12 ;AD13;AD14;AD15;AD16;AD17;AD18)<=1;COUNT(AE4;AE5;A E6;AE7;AE8;AE9;AE10;AE11;AE12;AE13;AE14;AE15;AE16; AE17;AE18)<=1;COUNT(AF4;AF5;AF6;AF7;AF8;AF9;AF10;A F11;AF12;AF13;AF14;AF15;AF16;AF17;AF18)<=1;COUNT(A G4;AG5;AG6;AG7;AG8;AG9;AG10;AG11;AG12;AG13;AG14;AG 15;AG16;AG17;AG18)<=1;COUNT(AH4;AH5;AH6;AH7;AH8;AH 9;AH10;AH11;AH12;AH13;AH14;AH15;AH16;AH17;AH18)<=1 ;COUNT(AI4;AI5;AI6;AI7;AI8;AI9;AI10;AI11;AI12;AI13 ;AI14;AI15;AI16;AI17;AI18)<=1;COUNT(AJ4;AJ5;AJ6;AJ 7;AJ8;AJ9;AJ10;AJ11;AJ12;AJ13;AJ14;AJ15;AJ16;AJ17; AJ18)<=1;COUNT(AK4;AK5;AK6;AK7;AK8;AK9;AK10;AK11;A K12;AK13;AK14;AK15;AK16;AK17;AK18)<=1) Can somebody give me an equivalent for this function , to be more simply ? 1.) Every count function must work like an normal Count function, not like COUNTIF function with criteria<=1 ! 2.)Like this doesn't help me (COUNT(AG4:AG18)<=1;COUNT(AK4:AK18)<=1;........... .), because this function is for exemple , I must made and built milions af this function , with references in combinatoric order ! 3.) Every count function is a combination of 15 numbers , used for an lotto statistic database , it doesn,t work to count { COUNT(AH4;AI4;AJ4;AK4)................., the drow is in an one column , not in one row . Thanks very much for your amability and for your time ! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FUNCTION in easyer format
Thanks very much sir Joel , but , how I said :
2.)Like this doesn't help me (COUNT(AG4:AG18)<=1;COUNT(AK4:AK18)<=1;........... .), because this function is for exemple , I must made and built milions af this function , with references in combinatoric order ! ............. I shall have 1;3;6 1;5;9 for eg .........for rows ; and I have to built milions of this kind of functions , with references in COMBINATORIC order ;nobody could to help me , I get some ideeas , but don't work : =COUNT(INDEX(MATCH(ROW(B4:AH83), {4,7,9,11,14,16,18,22,25,35,46,57,68,72,83},0)/ ISNUMBER(B4:AH83),0)) =SUMPRODUCT(ISNUMBER(MATCH(ROW(B4:AH83), {4,7,9,11,14,16,18,22,25,35,46,57,68,72,83},0))* ISNUMBER(B4:AH83)) =SUMPRODUCT(ISNUMBER(MATCH(ROW(B1:F5), {1,3,5},0))*(B1:F5<=1)) for this function posted of me in another thread : =AND(COUNT(B1;B3;B5)<=1;COUNT(C1;C3;C3)<=1;COUNT(D 1;D3;D5)<=1;COUNT(E1;E3;E* 5)<=1;COUNT(F1;F3;F5)<=1) Many thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
FUNCTION in easyer format
I think you need to do what you want using VBA instead of worksheet
functions. I can't tell exactly what you want from you vague examples. You are looking for combinatorial function which can be coded in VBA. I've done it before many times. Need more info to help write this code. "ytayta555" wrote: Thanks very much sir Joel , but , how I said : 2.)Like this doesn't help me (COUNT(AG4:AG18)<=1;COUNT(AK4:AK18)<=1;........... .), because this function is for exemple , I must made and built milions af this function , with references in combinatoric order ! ............. I shall have 1;3;6 1;5;9 for eg .........for rows ; and I have to built milions of this kind of functions , with references in COMBINATORIC order ;nobody could to help me , I get some ideeas , but don't work : =COUNT(INDEX(MATCH(ROW(B4:AH83), {4,7,9,11,14,16,18,22,25,35,46,57,68,72,83},0)/ ISNUMBER(B4:AH83),0)) =SUMPRODUCT(ISNUMBER(MATCH(ROW(B4:AH83), {4,7,9,11,14,16,18,22,25,35,46,57,68,72,83},0))* ISNUMBER(B4:AH83)) =SUMPRODUCT(ISNUMBER(MATCH(ROW(B1:F5), {1,3,5},0))*(B1:F5<=1)) for this function posted of me in another thread : =AND(COUNT(B1;B3;B5)<=1;COUNT(C1;C3;C3)<=1;COUNT(D 1;D3;D5)<=1;COUNT(E1;E3;EĀ* 5)<=1;COUNT(F1;F3;F5)<=1) Many thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
FUNCTION in easyer format
On 4 Iun, 13:19, Joel wrote:
Need more info to help write this code. Thanks very much for reply , sir Joel I'ts a long story . I have a lotto statistic database of 231 workbooks now .In every wbook I have 65446 Count functions , with the 15 references in combinatoric order (placed in column B ,row 91 to 65536 ); actually , I use autofill method for every wbook to find which combination of numbers hasn't =2 numbers in last continuous 33 draws (combined with an Countif function placed in Column BD ); an VBA macro loop trough Column BD and if the result of Countif function is =33 , then copy antyre row and paste it in another wbook ;in every wbook in range A1:BB90 are last 53 draws of this lottery ;this is the kind and wey of the querry in this database for to find the combinations of 15 numbers (from 90) which has in 33 continuous draws results of <2 numbers ;the autofill is from B91:B65536 to BB91:BB65536 , and it take 2 minute time for every workbook to end the querry in every wbook .Actually , I have 15,000,000 count functions in the 231 wbooks ; here begin another story , how I can built them ....I find a method after months of searchings , with an VBA code , which allow you to built 65536 Count functions (with the 15 references in combinatoric order! ) in some few minutes .Here is what I have actually . Normally , I look and search for speed ; a bigger speed mean a bigger database ! with the new formula I have posted here at the begin of this thread , I only need 4 or 5 columns of this kind of functions in every wbook , and no more autofill method ; in this wey , VBA macro only open the wbook , loops through an AND function placed in Column BD 91:BD65536 , and if the value of cell is TRUE , copy antire row and paste it in another wbook ! In this wey , with the same resource sistem , I'll have a database of 5 size bigger ; And now I get to the reason of this thread , why I need an equivalent for this function , (if even it works perfect for me ! ) . If i have an equivalent for this function , in a kind like this , for eg. : =COUNT(INDEX(MATCH(ROW(B4:AH83), {4,7,9,11,14,16,18,22,25,35,46,57,68,72,83},0)/ ISNUMBER(B4:AH83),0)) , ... I,ll can built this functions more easyer with this method I found ( with that VBA macro ! ...) . To understand perfect I must send you some wbooks to see clarly how I built this functions , and I can send you them .The best and fastest wey I think to be to give you my messenger ID , there I can send you the wbooks and explain instantly .My messenger ID is , if you agree ! Thanks very much for your time ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format within a Function | Excel Worksheet Functions | |||
format function | Excel Worksheet Functions | |||
how do you write format results of a function within a function? | Excel Worksheet Functions | |||
format & function | Excel Discussion (Misc queries) | |||
subtotals (85 coll): Is there an easyer way than ticking 85 boxes | Excel Discussion (Misc queries) |