Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
A good day !
I have this function : =AND(COUNT(B1;B3;B5)<=1;COUNT(C1;C3;C3)<=1;COUNT(D 1;D3;D5)<=1;COUNT(E1;E3;E5)<=1;COUNT(F1;F3;F5)<=1) ; this function let me to know if every one of the 5 COUNT functions have the result the value <=1; (this is what I need !!, not to count everyone of the five count functions the value only <=1!!!; it must work like Count functions, not like a Countif function {Countif <=1 }) ; The last result of this this function I want to show me if all 5 Count functions result is <=1 ; This function work perfect for me , but I need very very strong a perfect equivalent for this function (why I need this , it's a long story ...) ,made with another functions . I get some variants , but them doesn't work ;(but I think the solution is somewhere here arround) : =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)) or =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)) or =SUMPRODUCT(ISNUMBER(MATCH(ROW(B1:F5), {1,3,5},0))*(B1:F5<=1)) Can you help me to get a function which do the same thing like this function ? Thanks very much for your time . |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
You STILL have not explained why your first formula is not suitable
for you - you said it works perfectly for you. Pete On Jul 2, 4:05*pm, ytayta555 wrote: A good day ! I have this function *: =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 ) * ; this function let me to know if every one of the 5 COUNT functions have the result the value <=1; (this is what I need !!, not to count everyone of the five count functions the value only *<=1!!!; it must work like Count functions, not like a Countif function *{Countif <=1 }) ; The last result of this this function I want to show me if all *5 Count functions result is *<=1 ; This function work perfect for me , but I need very very strong a perfect equivalent for this function (why I need this , it's a long story ...) ,made with another functions . I get some variants , but them doesn't work ;(but I think the solution is somewhere here arround) : =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)) or =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)) or =SUMPRODUCT(ISNUMBER(MATCH(ROW(B1:F5), {1,3,5},0))*(B1:F5<=1)) Can you help me to get a function which do the same thing like this function ? Thanks very much for your time . |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
On 2 Iul, 19:11, Pete_UK wrote:
You STILL have not explained why your first formula is not suitable for you - you said it works perfectly for you. Thanks , mr. Pete I'ts a long long story ! ... Imagine that you need to make / built milions of this kind of functions ! milions of functions ... imagine this thing ! the references in this functions will be in combinatoric order ; I use an VBA macro to built this kind of functions (I found the resolve after many months of search .....) if I'll find another equivalent for this function I'm shure Ill can built them more easy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
ytayta555 wrote...
I have this function *: =AND(COUNT(B1;B3;B5)<=1;COUNT(C1;C3;C3)<=1; COUNT(D1;D3;D5)<=1;COUNT(E1;E3;E5)<=1;COUNT(F1;F3 ;F5)<=1) This is a formula involving many functions, not one function. this function let me to know if every one of the 5 COUNT functions have the result the value <=1; ... The last result of this this function I want to show me if all *5 Count functions result is *<=1 ; This function work perfect for me , but I need very very strong a perfect equivalent for this function . . . ... So you have a range like B1:F5 from which you want to count numbers in each column separately for rows 1, 3 and 5 only. You could try array formulas like =AND(MMULT(TRANSPOSE(ROW(rng))^0,ISNUMBER(rng)*{1; 0;1;0;1})<=1) [NOTE: US standard syntax - comma (,) as argument separator, semicolon (;) as ROW separator in array constants. Change as needed for your locale's syntax.] |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
On 2 Iul, 21:15, Harlan Grove wrote:
Thanks very much , I'll work and I'll comunicate later what result I get ; please very much to watch the topic in future , you gived me a wey very important for me . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
I don't understand why function ISNUMBER must to be use , I think is a
mistake ; I tried this formula : =AND(COUNT(MATCH(ROWS(B1:F5);{1;3;5})=1)) , but when you work to formula excel show you that the range in formula is B1:D5 , not like in my first formula B1;3;5 ; C1;3;5 ; D1;3;5; E1; .......etc . I have in workbook in cell B1 value = 1 , C3 =1 , D5= 1000 (for eg.) .When I add in cell D1 value = 5 , my function become FALSE (what it is right) , and this formula remane still TRUE (what it is wrong ). I think the solution is somewhere arround , in this steps : AND function first , then COUNT , then the combination of ROW and MATCH functions , or another functions which give you the references identically for this 5 Count functions ; really , I don't understand what can do there ISNUMBER function , but I don't know ; Can anybody please to work for this function , please very much , I tried in hundred weys , but it still don't work . Thank you very much for your time |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
Sorry , I forget to say I use excel 2003 ,
Romania . |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
ANY IDEEA HERE , PLEASE ? ! ...
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
Re-post your original question.
Most of us are not going to download old messages just to find yours. Gord Dibben MS Excel MVP On Thu, 3 Jul 2008 09:52:54 -0700 (PDT), ytayta555 wrote: ANY IDEEA HERE , PLEASE ? ! ... |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
Gord Dibben <gorddibbATshawDOTca wrote...
Re-post your original question. Most of us are not going to download old messages just to find yours. .... Use Google Groups. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
ytayta555 wrote...
I don't understand why function ISNUMBER must to be use , I think is a mistake ; No. You're mistaken. The formula I proposed, =AND(MMULT(TRANSPOSE(ROW(rng))^0,ISNUMBER(rng)*{1; 0;1;0;1})<=1) *sums* rather than counts. It sums 1's in the separate columns of rng when cells in rng contain numbers, and 0's when cells in rng don't contain numbers. There's no way to make this MMULT approach work without using ISNUMBER. I tried this formula : =AND(COUNT(MATCH(ROWS(B1:F5);{1;3;5})=1)) , but when you work to formula excel show you that the range in formula is B1:D5 , not like in my first formula B1;3;5 ; *C1;3;5 ; D1;3;5; E1; .......etc. ... I was clear that I was using US formula syntax, which means my formula used commas (,) rather than semicolon (;) as argument separator, and semicolons (;) rather than something else as row separators in array constants. I don't recall what the corresponding character is for European locales which use semicolons as argument separators, but you could find out by typing BUT NOT ENTERING the formula =ROW(1:10) and pressing [F9] - the character separating the numbers would be your locale's array constant row separator. The reason the formula above doesn't work for you is that in your locale's formula syntax, the array constant {1;3;5} is the equivalent of {1,3,5} in US locale formula syntax. I mean {1;3;5} in US locale formula syntax, and IT'S UP TO **YOU** TO TRANSLATE THIS INTO YOUR OWN LOCALE'S FORMULA SYNTAX. If you can't figure out your own locale's formula syntax, change your formula above to =AND(COUNT(MATCH(ROWS(B1:F5);TRANSPOSE({1;3;5}))= 1)) |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
WAW
Thank you very very much mr. Harlan Grove Now I am working and I'll post my results in this night or tomorrow . Thank you so much , this formula is very very important for me . |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
=AND(MMULT(TRANSPOSE(ROW(rng))^0,ISNUMBER(rng)*{1; 0;1;0;1})<=1)
What I must write instead of rng ? ( (ROW(rng) and ISNUMBER(rng) ? |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
ytayta555 wrote...
=AND(MMULT(TRANSPOSE(ROW(rng))^0,ISNUMBER(rng)*{1 ;0;1;0;1})<=1) What I must write instead of *rng ? ( (ROW(rng) and *ISNUMBER(rng) *? rng was a placeholder for a range address. Replace it with B1:F5 from your original posting or some other range address. If you change the number of rows, you'll need to change the array constant so that it has the same number of rows. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
I, m so sorry , I cann't get the good result yet , but I'l try
and try , because , if you believe me or not , this formula is the problem of my life ; I explain again : I need three functions in formula to count normally (not like an countif <=1 function) , and another function to show me if all of this three count functions have the result <=1 ; My function work , but , this is .....I really need a perfect equivalent for it ; to explain why , I must explain step by step how I built milions of functions with references in combinatoric order with the help of VBA ; another thing , with this function : =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) , one only workbook have 200 Mb , what is enormous size for hundred and hundred of workbooks I have them need ... |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
ytayta555 wrote...
I, m so sorry , I cann't get the good result yet , but I'l try and try , . . . I explain again *: I need three functions in formula to count normally (not like an countif <=1 function) , and another function to show me if all of this three count functions have the result <=1 ; ... =AND(COUNT(B1;B3;B5)<=1;COUNT(C1;C3;C3)<=1;COUNT( D1;D3;D5)<=1; COUNT(E1;E3;E*5)<=1;COUNT(F1;F3;F5)<=1) ... Understood: one formula to determine whether ALL columns in some range (in this case B1:F5) had 0 or 1 numeric values in row 1, 3 and 5. I'll try to use your locale's formula syntax. The expression ISNUMBER(B1:F5)*MOD(ROW(B1:F5);2) evaluates to an array of 1's and 0's with the same dimensions as B1:F5 (so 5 rows and 5 columns), 1's when the corresponding cells in the odd numbered rows of B1:F3 (B1, B3, B5, C1, C3, C5, etc.) contain numberic values and 0's otherwise (so either corresponding cells in odd numbered rows of B1:F5 don't contain numbers or for all cells in even numbered rows). For example, if B1:F5 contained A #DIV/0! B #DIV/0! 5 6 TRUE 6 C #DIV/0! 6 1 D E 6 F #DIV/0! G H 3 2 5 where B1, B5 and E5 are blank, D1, F1, B3 and D4 are error values, D2 is boolean value, and the other cells are either numeric or text values, the formula =ISNUMBER(B1:F5)*MOD(ROW(B1:F5);2) would return the array 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 1 1 0 1 so with 1's in odd numbered rows where the corresponding cells in B1:F5 contained numeric values and 0's otherwise. You want to check each column separately. The expression TRANSPOSE(ROW(B1:F5))^0 returns a horizontal array of 1's where each row in the range B1:F5 has a corresponding column in the result. The array formula =MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*MOD (ROW(B1:F5);2)) then returns the sum of each column of the array returned by the expression ISNUMBER(B1:F5)*MOD(ROW(B1:F5);2). With the sample data from the previous example, the formula immediately above is equivalent to matrix multiplication of 1 1 1 1 1 as the left multiplicand by 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 1 1 0 1 as the right multiplicand, which returns the matrix product 0 2 2 0 1 This reflects that in the sample B1:F5 data, B1 is blank, B3 is an error value and B5 is blank, so there are no numeric values in B1, B3 and B5, so the result array's first column entry is 0. C1 is text, but C3 and C5 both contain numbers, so 2 numbers in the odd numbered rows of C1:C5, so the result array's second column entry is 2. And similarly for the other columns. Since columns C and D both have more than one numeric value in their respective odd numbered rows, the array formula =AND(MMULT(TRANSPOSE(ROW(B1:F5))^0; ISNUMBER(B1:F5)*MOD(ROW(B1:F5);2))<=1) retruns FALSE. On the other hand, if B1:F5 contained A 4 #DIV/0! B C #DIV/0! D 1 #DIV/0! 3 TRUE 6 3 E 1 #DIV/0! FALSE F with E1, B2, B3, E3, F4, D5 and E5 blank, D1, D2, C3 and B5 error values, F3 and C5 boolean values, B1, F1, C2, E2, D4 and F5 text values, and the remaining cells numeric values, ISNUMBER(B1:F5)*MOD(ROW(B1:F5);2) would evaluate to 0 1 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*MOD( ROW(B1:F5);2)) would evaluate to 0 1 1 0 0 and so the array formula =AND(MMULT(TRANSPOSE(ROW(B1:F5))^0; ISNUMBER(B1:F5)*MOD(ROW(B1:F5);2))<=1) would evaluate to TRUE. Next, B1:F5 just happens to begin in an odd-numbered row. Replacing the literal range reference B1:F5 with the placeholder rng and generalizing the MOD(..) term so that it always selects odd indexed rows (e.g., 1st, 3rd, 5th, 7th, etc. rows of rng whether rng's topmost row is in an odd or even numbered row) gives =AND( MMULT(TRANSPOSE(ROW( rng ))^0; ISNUMBER( rng ) *MOD(ROW( rng )-MIN(ROW( rng ))+1; 2 ) )<= 1 ) TRANSPOSE(ROW( rng ))^0 always returns a horizontal array of 1's with as many columns as rng has rows. ISNUMBER( rng ) is an array of TRUE and FALSE boolean values of the same size and shape as rng. When multiplied by the next term, Excel converts the TRUE values to 1's and the FALSE values to 0's. ROW( rng )-MIN(ROW( rng ))+1 is a vertical array of sequential integers from 1 to ROWS(rng). MOD(ROW( rng )-MIN(ROW( rng ))+1; 2 ) is a vertical array of 1's and 0's with 1's in odd indexed rows and 0's in even indexed rows. The product of the ISNUMBER and MOD function calls is an array of the same size and shape as rng with 1's correspond to cells in odd indexed rows of rng containing numeric values and 0's otherwise. The MMULT function call returns a horizontal array of the sums of each of the separate columns of the array that's the product of the ISNUMBER and MOD function calls. MMULT(..)<=1 returns a horizontal array of TRUE's and FALSE's with TRUE's for each entry in the MMULT(..) function call's result array less than or equal to 1, so for those columns in rng with 1 or 0 numeric values in the odd indexed rows of rng. The AND call returns TRUE if all columns of rng contain no more than 1 numeric value in the odd indexed rows. You could change this to, for example, every fifth row (so rows indexed 5, 10, 15, etc.) using the array formula =AND( MMULT(TRANSPOSE(ROW( rng ))^0; ISNUMBER( rng ) *(MOD(ROW( rng )-MIN(ROW( rng ))+1; 5 ) = 0 ) )<= 1 ) since the ROW( rng )-MIN(ROW( rng ))+1 expression is still a vertical array of sequential integers from 1 to ROWS(rng), so the expression MOD(ROW( rng )-MIN(ROW( rng ))+1; 5 ) becomes a vertical array cyclically repeating integers 1, 2, 3, 4, 0. The expression (MOD(ROW( rng )-MIN(ROW( rng ))+1; 5 ) = 0 ) is a vertical array of TRUE's when the MOD(..) array result equals 0 and FALSE's otherwise. When this is multiplied by the ISNUMBER function call's result, Excel converts the TRUE's to 1's and the FALSE's to 0's. If rng were B2:E19, MOD(ROW( rng )-MIN(ROW( rng ))+1; 5 ) = 0 would return FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE I've tested all these formulas. They *ALL* work as I've indicated. At this point, if you can't get them to work, you'd be better off seeking someone who understands your own language but has better comprehension of English (and, better still, of Excel) who could help you with what I've written. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
On 7 Iul, 02:12, Harlan Grove wrote:
Understood: one formula to determine whether ALL columns in some range IT'S JUST AN AMASING AND GREAT ANSWER Mr . Harlan Grove ! I have no words thinking to your time ... I apologise , I haven't time to work today , but I'll work and tell a little later my results ; please very much again to watch the topic in next few days ; if it is possible , and I'm not impolite , I'd like and think very constructive and highlighting to can send you an workbook with my formula and example of what formula must do , to an e-mail ; my personal e-mail is ; I'm shure - with the great way you gived me - , that I'm very very near by the right function !! Many thanks again , I think I'ts a rare answer ! ... |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
On 7 Iul, 02:12, Harlan Grove wrote:
I'TS AN AMASING AND GREAT ANSWER , Mr .Harlan Grove ! I have no words thinking to your time ! ... I apologise , I haven't time today to work , but I please you again to watch this topic in next one or two few days , I'll write my results; if I'm not impolite , and if it is possible , I'd like to have posibility to send you an workbook with some examples of what this formula must do [ I think this thing is very constructive for me and highlighting for this problem ] ; my e-mail is ; I feel I'm very very near by getting right formula , with the great way you gived me ! Thanks again ! I consider it's a rare answer ! ... |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
Incredible news : I tested all formulas , and ALL work great .
I cann't still believe that I get what I needed so strong ,by so along time ..... In all formulas , my rng will be B1:F90 , but my actually problem which it appears in my front is how to built the references in combinatoric order , because most of my future functions will have references not in odd order , and 10 references(rows). examples : __________________________________________________ ______________ 1). If I get the perfect equivalent for this formula , I believe I'll understand how to work for changes of references in combinatoric order : =AND(COUNT(b1;b3;b5;b6;b7;b10;b12;b15;b18;b38)<=1; COUNT(c1;c3;c5;c6;c7;c10;c12;c15;c18;c38)<=1;COUNT (d1;d3;d5;d6;d7;d10;d12;d15;d18;d38);COUNT(e1;e3;e 5;e6;e7;e10;e12;e15;e18;e38);COUNT(f1;f3;f5;f6;f7; f10;f12;f15;f18;f38)<=1) I understand I must work with *MOD(ROW( rng )-MIN(ROW( rng ))+1; 2 ) , with MIN function , but I don't know yet how __________________________________________________ ______________ 2). In a perfect world , a perfect dream is to make to work this formula : =AND(COUNT(MATCH(ROWS(B1:F5);TRANSPOSE({1;3;5}))= 1)) (My rng is in all formulas B1:F90 , and the array is very easy to work with it for make references in combinatoric order ) . for example , we take b1:f5 values from your first example : Col: | B C D E F __________________________________________________ _____________ Row1| A #DIV/0! B #DIV/0! Row2| 5 6 TRUE 6 C Row3| #DIV/0! 6 1 D E Row4| 6 F #DIV/0! G H Row5| 3 2 5 When I delete Range(C3:D3) , your formula : {=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5 )*MOD(ROW(B1:F5); 2))<=1)} , which work perfect , became TRUE , what it is wright ! the formula above , still remane FALSE , it don't work !! I tryed and so , but the same negative result : {=AND(COUNT(MATCH(ROWS(B1:F5);TRANSPOSE({1;3;5})<= 1)))} __________________________________________________ ______________ 3). Question , only and only if it is posible : I have placeholder rng for all f ormulas :B1:F90 ! Can work the equivalent for this function : =AND(COUNT(b1;b3;b5;b6;b7;b10;b12;b15;b18;b38)<=1; COUNT(c1;c3;c5;c6;c7;c10;c12;c15;c18;c38)<=1;COUNT (d1;d3;d5;d6;d7;d10;d12;d15;d18;d38);COUNT(e1;e3;e 5;e6;e7;e10;e12;e15;e18;e38);COUNT(f1;f3;f5;f6;f7; f10;f12;f15;f18;f38)<=1) with rng B1:F90 or only with range B1:F38 ? only if it is posible this ..... __________________________________________________ ______________ 4). Another variants , I'm not shure which will work : =AND(COUNT(INDEX(MATCH(ROW(B1:F5), {1;3;5},0)/ ISNUMBER(B1:F5),0))) or =SUMPRODUCT(ISNUMBER(MATCH(ROW(B1:F5), {1,3,5},0))*(B1:F5<=1)) or =SUMPRODUCT(ISNUMBER(MATCH(ROW(B1:F5), {1;3;5},0))* ISNUMBER(B1:F5)) __________________________________________________ ______________ I have no words to apreciate your time and knowledge used for me |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLD PROBLEM without SOLUTION
Mr. Harlan Grove (and not only) can you take a look for this my last
problem ? 1). and 2). Many thanks for this incredible help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need A Solution To A Problem | Excel Discussion (Misc queries) | |||
sum if problem, trying to find best solution | Excel Discussion (Misc queries) | |||
Need solution to formula problem | Excel Discussion (Misc queries) | |||
Macro Solution for Link Problem? | Excel Worksheet Functions | |||
Pesky little problem with no solution? | Excel Worksheet Functions |