| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array help Part 2
Almost got it !
from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G1&H1)+0,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
maybe you need to re-post on other forum
"driller" wrote: Almost got it ! from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G1&H1)+0,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
The probelm is this portion:
(F1&G1&H1)+0 Where the values are the text entries: X y Z The math operation of adding 0 causes the #VALUE! error. (XyZ)+0 = #VALUE! Change this: (F1&G1&H1)+0 To: IF(ISERROR((F1&G1&H1)+0),F1&G1&H1,(F1&G1&H1)+0) Biff "driller" wrote in message ... Almost got it ! from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G1&H1)+0,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
Almost got it Biff,,,but when i try to change
C2: X into X1, D2: y into Y1, E2: Z into Z1, the result on Col.B seems unusual... pls. try again ! thanks....more power "Biff" wrote: The probelm is this portion: (F1&G1&H1)+0 Where the values are the text entries: X y Z The math operation of adding 0 causes the #VALUE! error. (XyZ)+0 = #VALUE! Change this: (F1&G1&H1)+0 To: IF(ISERROR((F1&G1&H1)+0),F1&G1&H1,(F1&G1&H1)+0) Biff "driller" wrote in message ... Almost got it ! from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G1&H1)+0,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
the result on Col.B seems unusual...
What result did you get that's unusual? When I change C2:E2 to X1, Y1, Z1 I still get the expected results. The formula works for what it was designed for. You may be expecting it to do things that it isn't intended to do! Biff "driller" wrote in message ... Almost got it Biff,,,but when i try to change C2: X into X1, D2: y into Y1, E2: Z into Z1, the result on Col.B seems unusual... pls. try again ! thanks....more power "Biff" wrote: The probelm is this portion: (F1&G1&H1)+0 Where the values are the text entries: X y Z The math operation of adding 0 causes the #VALUE! error. (XyZ)+0 = #VALUE! Change this: (F1&G1&H1)+0 To: IF(ISERROR((F1&G1&H1)+0),F1&G1&H1,(F1&G1&H1)+0) Biff "driller" wrote in message ... Almost got it ! from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G1&H1)+0,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
Biff, here is the complete formula
B1=IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),IF(ISERROR((F1&G 1&H1)+0),F1&G1&H1,(F1&G1&H1)+0),"") then here is the table with result on Col. B A B C D E F G H 1 1 * 0 3 5 9 2 X1 y1 Z1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 XyZ X y Z 7 1 6 4 8 zZ* z Z * 9 4 4 4 10 950 9 5 0 11 XxZ X x Z From Above :B6, B8 and B11, has nothing to do with data from C2:E2 the requested benefit here is the that the data on C2:E2, must accomodate any number of character in each cell, which may be assigned as cell refs. like IV65536 or search for text strings...by modifying the concatenated result. Is it possible ? Thanks "Biff" wrote: the result on Col.B seems unusual... What result did you get that's unusual? When I change C2:E2 to X1, Y1, Z1 I still get the expected results. The formula works for what it was designed for. You may be expecting it to do things that it isn't intended to do! Biff "driller" wrote in message ... Almost got it Biff,,,but when i try to change C2: X into X1, D2: y into Y1, E2: Z into Z1, the result on Col.B seems unusual... pls. try again ! thanks....more power "Biff" wrote: The probelm is this portion: (F1&G1&H1)+0 Where the values are the text entries: X y Z The math operation of adding 0 causes the #VALUE! error. (XyZ)+0 = #VALUE! Change this: (F1&G1&H1)+0 To: IF(ISERROR((F1&G1&H1)+0),F1&G1&H1,(F1&G1&H1)+0) Biff "driller" wrote in message ... Almost got it ! from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G1&H1)+0,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
excuse me Biff, are you still around ? I'll catch back to this thread
tomorrow or either re-post it tomorrow, if you dont mind...I'll go for a break...thanks for trying... "Biff" wrote: the result on Col.B seems unusual... What result did you get that's unusual? When I change C2:E2 to X1, Y1, Z1 I still get the expected results. The formula works for what it was designed for. You may be expecting it to do things that it isn't intended to do! Biff "driller" wrote in message ... Almost got it Biff,,,but when i try to change C2: X into X1, D2: y into Y1, E2: Z into Z1, the result on Col.B seems unusual... pls. try again ! thanks....more power "Biff" wrote: The probelm is this portion: (F1&G1&H1)+0 Where the values are the text entries: X y Z The math operation of adding 0 causes the #VALUE! error. (XyZ)+0 = #VALUE! Change this: (F1&G1&H1)+0 To: IF(ISERROR((F1&G1&H1)+0),F1&G1&H1,(F1&G1&H1)+0) Biff "driller" wrote in message ... Almost got it ! from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G1&H1)+0,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
I don't know what you're asking about here.
That formula was written for a *specific situation* and by changing the data it no longer fits that *specific situation*. Biff "driller" wrote in message ... excuse me Biff, are you still around ? I'll catch back to this thread tomorrow or either re-post it tomorrow, if you dont mind...I'll go for a break...thanks for trying... "Biff" wrote: the result on Col.B seems unusual... What result did you get that's unusual? When I change C2:E2 to X1, Y1, Z1 I still get the expected results. The formula works for what it was designed for. You may be expecting it to do things that it isn't intended to do! Biff "driller" wrote in message ... Almost got it Biff,,,but when i try to change C2: X into X1, D2: y into Y1, E2: Z into Z1, the result on Col.B seems unusual... pls. try again ! thanks....more power "Biff" wrote: The probelm is this portion: (F1&G1&H1)+0 Where the values are the text entries: X y Z The math operation of adding 0 causes the #VALUE! error. (XyZ)+0 = #VALUE! Change this: (F1&G1&H1)+0 To: IF(ISERROR((F1&G1&H1)+0),F1&G1&H1,(F1&G1&H1)+0) Biff "driller" wrote in message ... Almost got it ! from previous question of Luke "Array Help" yesterday, where the checked answer is : on B1 =IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),(F1&G1&H1)+0,"") Please reply for an answer that is good for the following arrangement. A B C D E F G H 1 1 * 0 3 5 9 2 X y Z 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 #value! X y Z 7 1 6 4 8 #value! z Z * 9 4 4 4 10 950 9 5 0 11 #value! X x Z I hope it's easy to modify for advance learning...thanks... |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
Since it appears you want a partial, case sensitive search for the reference
range C1:E3, try this modification which uses FIND instead of MATCH In B1, copied down: =IF(AND(SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,F1))),SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,G1))),SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,H1)))),IF(ISERROR((F1&G1&H 1)+0),F1&G1&H1,(F1&G1&H1)+0),"") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "driller" wrote in message ... Biff, here is the complete formula B1=IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),IF(ISERROR((F1&G 1&H1)+0),F1&G1&H1,(F1&G1&H1)+0),"") then here is the table with result on Col. B A B C D E F G H 1 1 * 0 3 5 9 2 X1 y1 Z1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 XyZ X y Z 7 1 6 4 8 zZ* z Z * 9 4 4 4 10 950 9 5 0 11 XxZ X x Z From Above :B6, B8 and B11, has nothing to do with data from C2:E2 the requested benefit here is the that the data on C2:E2, must accomodate any number of character in each cell, which may be assigned as cell refs. like IV65536 or search for text strings...by modifying the concatenated result. Is it possible ? Thanks |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
welcome back, max, at least the 999 has re-acted responsively <bg,
For this post, you almost got it - Case sensitive - I paste the formula and it grab X1Y1Z1, but it is static - when I try to test run the formula by changing data as follows.... from C2 : X1 into X2 from D2 : y1 into y2 from E2 : Z1 into Z2 from F6 : X into X1 from G6 : y into y1 from H6 : Z into Z1 then on B6 : the result is X1y1Z1 ? the individual cellS on C1:E3 do not contain these since I change it already with suffix no 2. Is it static or some formula modification is needed...Pls. take note that the data on C1:E3 should contain varying numbers of character (like cell refs A1-IV65536) thanks and pls. dont hang up... "Max" wrote: Since it appears you want a partial, case sensitive search for the reference range C1:E3, try this modification which uses FIND instead of MATCH In B1, copied down: =IF(AND(SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,F1))),SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,G1))),SUMPRODUCT(--ISNUMBER(FIND($C$1:$E$3,H1)))),IF(ISERROR((F1&G1&H 1)+0),F1&G1&H1,(F1&G1&H1)+0),"") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "driller" wrote in message ... Biff, here is the complete formula B1=IF(AND(SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,F1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,G1,0))),SUMPRODUCT(--ISNUMBER(MATCH($C$1:$E$3,H1,0)))),IF(ISERROR((F1&G 1&H1)+0),F1&G1&H1,(F1&G1&H1)+0),"") then here is the table with result on Col. B A B C D E F G H 1 1 * 0 3 5 9 2 X1 y1 Z1 7 4 4 3 100 9 8 5 1 0 0 4 2 1 3 5 6 4 3 6 XyZ X y Z 7 1 6 4 8 zZ* z Z * 9 4 4 4 10 950 9 5 0 11 XxZ X x Z From Above :B6, B8 and B11, has nothing to do with data from C2:E2 the requested benefit here is the that the data on C2:E2, must accomodate any number of character in each cell, which may be assigned as cell refs. like IV65536 or search for text strings...by modifying the concatenated result. Is it possible ? Thanks |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
Perhaps using EXACT will do it here, conclusively ..
In B1, copied down: =IF(AND(SUMPRODUCT(--(EXACT($C$1:$E$3,F1))),SUMPRODUCT(--(EXACT($C$1:$E$3,G1))),SUMPRODUCT(--(EXACT($C$1:$E$3,H1)))),IF(ISERROR((F1&G1&H1)+0),F 1&G1&H1,(F1&G1&H1)+0),"") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "driller" wrote in message ... welcome back, max, at least the 999 has re-acted responsively <bg, For this post, you almost got it - Case sensitive - I paste the formula and it grab X1Y1Z1, but it is static - when I try to test run the formula by changing data as follows.... from C2 : X1 into X2 from D2 : y1 into y2 from E2 : Z1 into Z2 from F6 : X into X1 from G6 : y into y1 from H6 : Z into Z1 then on B6 : the result is X1y1Z1 ? the individual cellS on C1:E3 do not contain these since I change it already with suffix no 2. Is it static or some formula modification is needed...Pls. take note that the data on C1:E3 should contain varying numbers of character (like cell refs A1-IV65536) thanks and pls. dont hang up... |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
.. then on B6 : the result is X1y1Z1 ?
The prob you faced was because you had a "1" in C1 (within the reference range C1:E3) which would be found in "X1", "y1", "Z1" (in F6:H6) by FIND Try the EXACT rendition given earlier. Seems to work fine. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
thanks for reply,
i tried as u suggested and found again this part of the formula =exact($C$1:$E$3,F6) result is #value! i format tested the X1, Y1, Z1 on C3:E3 and F6:H6 , copy paste same format and value as general and test it all as text... i am confused now..with sumproduct giving a result of 1 again. Does sumproduct read the #value! as 1 under any formulation. "Max" wrote: .. then on B6 : the result is X1y1Z1 ? The prob you faced was because you had a "1" in C1 (within the reference range C1:E3) which would be found in "X1", "y1", "Z1" (in F6:H6) by FIND Try the EXACT rendition given earlier. Seems to work fine. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
"driller" wrote:
thanks for reply, i tried as u suggested and found again this part of the formula =exact($C$1:$E$3,F6) result is #value! i format tested the X1, Y1, Z1 on C3:E3 and F6:H6 , copy paste same format and value as general and test it all as text... i am confused now..with sumproduct giving a result of 1 again. Does sumproduct read the #value! as 1 under any formulation. First, trust the EXACT formula given earlier worked ok for you, right ? Please confirm this Ok, as regards your dissection observations above, this is the correct way to observe the evaluation process Let's take this part of the formula in B6: SUMPRODUCT(--(EXACT($C$1:$E$3,F6))) (with X1, y1, Z1 entered in C3:E3) In the formula bar for B6, carefully select only the part: EXACT($C$1:$E$3,F6) then press F9 key to evaluate You would see that it evaluates to a series of FALSEs / TRUEs, ie: {FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;TRUE,FALSE,FA LSE} Press Esc key to revert, now carefully select only the part: --(EXACT($C$1:$E$3,F6)) then press F9 again With the double unary wrapped around, viz: --(EXACT(...)), the series of FALSEs / TRUEs returned by EXACT will be coerced (evaluated) to a series of 0s / 1s, ie the results will appear as: {0,0,0;0,0,0;1,0,0} (FALSE =0, TRUE = 1) Press Esc again to revert, now select only the part: SUMPRODUCT(--(EXACT($C$1:$E$3,F6))) then press F9 again The SUMPRODUCT(...) will evaluate the series of 0s / 1s, viz: {0,0,0;0,0,0;1,0,0} to return the final result of: 1 If we place in a cell and just press ENTER: =EXACT($C$1:$E$3,F6) we'd get the wrong result of: #VALUE! because the expression is an array and needs to be array-entered by pressing CTRL+SHIFT+ENTER. If we array-enter it correctly, ie: =EXACT($C$1:$E$3,F6) then the expression will evaluate correctly, *but* the cell will display only the leftmost value in the array returned, ie all we'd see in the cell is: FALSE In order to see the entire array returned, we'd need to select the array-entered expression in the formula bar, viz select: {=EXACT($C$1:$E$3,F6)} then press F9 Note: The curly braces: { } are auto-inserted by Excel upon correct array-entering. We can use this as a visual check in the formula bar for any array-entered formulas. Pressing F9 will now reveal the entire array returned: {FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;TRUE,FALSE,FA LSE} Hope the above clarifies it a little better here .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
One main versatility in using SUMPRODUCT is that we don't need* to
array-enter the expression for it to evaluate the nested arrays within. *Except perhaps if TRANSPOSE is nested within the SD, viz: =SUMPRODUCT(TRANSPOSE(...) ...) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
Very comprehensively explained. Well done Max!
-- Regards Roger Govier "Max" wrote in message ... "driller" wrote: thanks for reply, i tried as u suggested and found again this part of the formula =exact($C$1:$E$3,F6) result is #value! i format tested the X1, Y1, Z1 on C3:E3 and F6:H6 , copy paste same format and value as general and test it all as text... i am confused now..with sumproduct giving a result of 1 again. Does sumproduct read the #value! as 1 under any formulation. First, trust the EXACT formula given earlier worked ok for you, right ? Please confirm this Ok, as regards your dissection observations above, this is the correct way to observe the evaluation process Let's take this part of the formula in B6: SUMPRODUCT(--(EXACT($C$1:$E$3,F6))) (with X1, y1, Z1 entered in C3:E3) In the formula bar for B6, carefully select only the part: EXACT($C$1:$E$3,F6) then press F9 key to evaluate You would see that it evaluates to a series of FALSEs / TRUEs, ie: {FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;TRUE,FALSE,FA LSE} Press Esc key to revert, now carefully select only the part: --(EXACT($C$1:$E$3,F6)) then press F9 again With the double unary wrapped around, viz: --(EXACT(...)), the series of FALSEs / TRUEs returned by EXACT will be coerced (evaluated) to a series of 0s / 1s, ie the results will appear as: {0,0,0;0,0,0;1,0,0} (FALSE =0, TRUE = 1) Press Esc again to revert, now select only the part: SUMPRODUCT(--(EXACT($C$1:$E$3,F6))) then press F9 again The SUMPRODUCT(...) will evaluate the series of 0s / 1s, viz: {0,0,0;0,0,0;1,0,0} to return the final result of: 1 If we place in a cell and just press ENTER: =EXACT($C$1:$E$3,F6) we'd get the wrong result of: #VALUE! because the expression is an array and needs to be array-entered by pressing CTRL+SHIFT+ENTER. If we array-enter it correctly, ie: =EXACT($C$1:$E$3,F6) then the expression will evaluate correctly, *but* the cell will display only the leftmost value in the array returned, ie all we'd see in the cell is: FALSE In order to see the entire array returned, we'd need to select the array-entered expression in the formula bar, viz select: {=EXACT($C$1:$E$3,F6)} then press F9 Note: The curly braces: { } are auto-inserted by Excel upon correct array-entering. We can use this as a visual check in the formula bar for any array-entered formulas. Pressing F9 will now reveal the entire array returned: {FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;TRUE,FALSE,FA LSE} Hope the above clarifies it a little better here .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
thanks to all who help,,,
i just found out a milder formula like this with a CSE =IF(AND(SUM(--(EXACT($C$1:$E$3,F6))),SUM(--(EXACT($C$1:$E$3,G6))),SUM(--(EXACT($C$1:$E$3,H6)))),IF(ISERROR((F6&G6&H6)+0),F 6&G6&H6,(F6&G6&H6)+0),"") cheers , more power ^2!!! "Roger Govier" wrote: Very comprehensively explained. Well done Max! -- Regards Roger Govier "Max" wrote in message ... "driller" wrote: thanks for reply, i tried as u suggested and found again this part of the formula =exact($C$1:$E$3,F6) result is #value! i format tested the X1, Y1, Z1 on C3:E3 and F6:H6 , copy paste same format and value as general and test it all as text... i am confused now..with sumproduct giving a result of 1 again. Does sumproduct read the #value! as 1 under any formulation. First, trust the EXACT formula given earlier worked ok for you, right ? Please confirm this Ok, as regards your dissection observations above, this is the correct way to observe the evaluation process Let's take this part of the formula in B6: SUMPRODUCT(--(EXACT($C$1:$E$3,F6))) (with X1, y1, Z1 entered in C3:E3) In the formula bar for B6, carefully select only the part: EXACT($C$1:$E$3,F6) then press F9 key to evaluate You would see that it evaluates to a series of FALSEs / TRUEs, ie: {FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;TRUE,FALSE,FA LSE} Press Esc key to revert, now carefully select only the part: --(EXACT($C$1:$E$3,F6)) then press F9 again With the double unary wrapped around, viz: --(EXACT(...)), the series of FALSEs / TRUEs returned by EXACT will be coerced (evaluated) to a series of 0s / 1s, ie the results will appear as: {0,0,0;0,0,0;1,0,0} (FALSE =0, TRUE = 1) Press Esc again to revert, now select only the part: SUMPRODUCT(--(EXACT($C$1:$E$3,F6))) then press F9 again The SUMPRODUCT(...) will evaluate the series of 0s / 1s, viz: {0,0,0;0,0,0;1,0,0} to return the final result of: 1 If we place in a cell and just press ENTER: =EXACT($C$1:$E$3,F6) we'd get the wrong result of: #VALUE! because the expression is an array and needs to be array-entered by pressing CTRL+SHIFT+ENTER. If we array-enter it correctly, ie: =EXACT($C$1:$E$3,F6) then the expression will evaluate correctly, *but* the cell will display only the leftmost value in the array returned, ie all we'd see in the cell is: FALSE In order to see the entire array returned, we'd need to select the array-entered expression in the formula bar, viz select: {=EXACT($C$1:$E$3,F6)} then press F9 Note: The curly braces: { } are auto-inserted by Excel upon correct array-entering. We can use this as a visual check in the formula bar for any array-entered formulas. Pressing F9 will now reveal the entire array returned: {FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;TRUE,FALSE,FA LSE} Hope the above clarifies it a little better here .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
Dear Roger Govier,
try my reply to Max ....This is a bubble gum "double coercion". "Roger Govier" wrote: Very comprehensively explained. Well done Max! -- Regards Roger Govier "Max" wrote in message ... "driller" wrote: thanks for reply, i tried as u suggested and found again this part of the formula =exact($C$1:$E$3,F6) result is #value! i format tested the X1, Y1, Z1 on C3:E3 and F6:H6 , copy paste same format and value as general and test it all as text... i am confused now..with sumproduct giving a result of 1 again. Does sumproduct read the #value! as 1 under any formulation. First, trust the EXACT formula given earlier worked ok for you, right ? Please confirm this Ok, as regards your dissection observations above, this is the correct way to observe the evaluation process Let's take this part of the formula in B6: SUMPRODUCT(--(EXACT($C$1:$E$3,F6))) (with X1, y1, Z1 entered in C3:E3) In the formula bar for B6, carefully select only the part: EXACT($C$1:$E$3,F6) then press F9 key to evaluate You would see that it evaluates to a series of FALSEs / TRUEs, ie: {FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;TRUE,FALSE,FA LSE} Press Esc key to revert, now carefully select only the part: --(EXACT($C$1:$E$3,F6)) then press F9 again With the double unary wrapped around, viz: --(EXACT(...)), the series of FALSEs / TRUEs returned by EXACT will be coerced (evaluated) to a series of 0s / 1s, ie the results will appear as: {0,0,0;0,0,0;1,0,0} (FALSE =0, TRUE = 1) Press Esc again to revert, now select only the part: SUMPRODUCT(--(EXACT($C$1:$E$3,F6))) then press F9 again The SUMPRODUCT(...) will evaluate the series of 0s / 1s, viz: {0,0,0;0,0,0;1,0,0} to return the final result of: 1 If we place in a cell and just press ENTER: =EXACT($C$1:$E$3,F6) we'd get the wrong result of: #VALUE! because the expression is an array and needs to be array-entered by pressing CTRL+SHIFT+ENTER. If we array-enter it correctly, ie: =EXACT($C$1:$E$3,F6) then the expression will evaluate correctly, *but* the cell will display only the leftmost value in the array returned, ie all we'd see in the cell is: FALSE In order to see the entire array returned, we'd need to select the array-entered expression in the formula bar, viz select: {=EXACT($C$1:$E$3,F6)} then press F9 Note: The curly braces: { } are auto-inserted by Excel upon correct array-entering. We can use this as a visual check in the formula bar for any array-entered formulas. Pressing F9 will now reveal the entire array returned: {FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;TRUE,FALSE,FA LSE} Hope the above clarifies it a little better here .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
Thanks for the compliments, Roger !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Roger Govier" wrote in message ... Very comprehensively explained. Well done Max! -- Regards Roger Govier |
|
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
"driller" wrote:
thanks to all who help You're welcome. i just found out a milder formula like this with a CSE =IF(AND(SUM(--(EXACT($C$1:$E$3,F6))),SUM(--(EXACT($C$1:$E$3,G6))),SUM(--(EXACT($C$1:$E$3,H6)))),IF(ISERROR((F6&G6&H6)+0),F 6&G6&H6,(F6&G6&H6)+0),"") cheers , more power ^2!!! It's a trade-off. Using SUM you do save on the keystrokes: "PRODUCT" x 3 = 21 keystrokes in total here, and that results in a shorter looking formula, but it requires array-entry (CSE) unlike using SUMPRODUCT which doesn't (normal ENTER will do). Just a matter of personal preference, of course <g. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
thanks for your personal count of key strokes
maybe a diffrence of 21-3CSE = 18 only... I just simply follow help files about array formulation with braces plus your smart build-up of formula of an IF's arrangement. -for classic reading. hope to learn more classic tricks. thanks again and looking forward to see Orchard again... "Max" wrote: "driller" wrote: thanks to all who help You're welcome. i just found out a milder formula like this with a CSE =IF(AND(SUM(--(EXACT($C$1:$E$3,F6))),SUM(--(EXACT($C$1:$E$3,G6))),SUM(--(EXACT($C$1:$E$3,H6)))),IF(ISERROR((F6&G6&H6)+0),F 6&G6&H6,(F6&G6&H6)+0),"") cheers , more power ^2!!! It's a trade-off. Using SUM you do save on the keystrokes: "PRODUCT" x 3 = 21 keystrokes in total here, and that results in a shorter looking formula, but it requires array-entry (CSE) unlike using SUMPRODUCT which doesn't (normal ENTER will do). Just a matter of personal preference, of course <g. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array help Part 2
maybe a difference of 21-3CSE = 18 only...
It's 19 only then, to be exact, for par comparison SP still requires an ENTER <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| defintion of array function | Excel Discussion (Misc queries) | |||
| Transpose words and numbers into array of different proportions | Excel Discussion (Misc queries) | |||
| Array not working correctly Returns FALSE on second part | Excel Discussion (Misc queries) | |||
| What instead of an array formula part 2 | Excel Discussion (Misc queries) | |||
| VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) | |||
Linear Mode
