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
---